Solved

Help me in SQL SERVER to MS ACCESS

Posted on 2003-11-11
13
407 Views
Last Modified: 2010-08-05
Hello friends,

I have a shopping web site done in asp with MS Acess as database

I have a Inventory system done by another person, in VB with SQL server as back end.

Now I want like this:

At the end of Each day,whatever the products that are entered in the VB system (backend sql server),that has to be reflected in the MS Access database.The DB structure of SQL SERVER and MSACCESS database are different totally.

In the MS ACCESS Database called

there are two table called 'products' and 'comp_note'

In the VB -> SQL SERVER ,there are no such tables

The main thing is that ,i have to create four columns newly in my MS ACCESS tables(on both the tables)
I have to take info on those 4 columns from that SQL Server DB.

Along with that whatever product is added in future in that inventory system, i have to append it in
my web database of MS Access


SO what im thinking of to do is :

(For a understanding of my view)

1.Using DTS to import the two tables,'products' and 'comp_note'( in the MS Access-latest version of the table.This includes  

  the extra four columns.)      

  This way the two tables would be created and records willl be stored in the SQL server.


2.when adding new records in the VB system,the columns required for these two tables would be actually
  coming from different tables(which are already existing).so i would select the required columns and then
  insert the data into the newly created two tables.



3.Then i will create a Upload button which should do the conversion of the two tables in SQL server
  to MS ACcess and that has to be uploaded to a directory inside the site.This two tables has to be uploaded on a daily    

basis.This is how the requirement is.

  Can we use addlinked server concept here? I dont have knowledge on this.please help me out

  Can any one tell me how to convert those tables alone into MS Access database and how to upload that without using FTP  

sort of things ?

  or is there any other way ?
  please help me out.Thanks
  Rajesh.R
0
Comment
Question by:RajaRajeswari
  • 7
  • 6
13 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 9722113
RajaRajeswari,

There are several ways to do what you want.

1. You may setup replication between SQL Server and Access
2. You may use Access VBA to retrive the data from SQL Server using ADO

I would suggest using the second method.

You will need to setup the missing fields in Access (if they do not exist, if they do, even in different tables, you will be able to place data there.).  Once this is done yo ucan open a ADO connection from Access to SQL Server and return a recordset from SQL Server, which you will then insert into the correct places in Access.

There is no need to use DTS.  If you wish to manipulate SQL Server objects directly (such as in method 1), take a look at SQL DMO.

Leon
0
 

Author Comment

by:RajaRajeswari
ID: 9729292
Hai leonstyker
thanks for ur suggestion
I have not explroed anything as i have been assigned some other job for the tim e being .once i start working on it , i will get on the real exp.
THanks for nw really.I will test it soon
Thanks
RR
0
 

Author Comment

by:RajaRajeswari
ID: 9729294
Hai leonstyker
thanks for ur suggestion
I have not explored anything as i have been assigned some other job for the tim e being .once i start working on it , i will get on the real exp.
THanks for nw really.I will test it soon
Thanks
RR
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9731119
Ok, let me know how it turns out,

Leon
0
 

Author Comment

by:RajaRajeswari
ID: 9785183
Hello Leon

NOw im working on the question i have posted.I have transferred first data from SQL Server to MS Access

Now i want to upload the Access database from VB.
My MS Access database is DSN based.

So now i have to upload the database's current data to the MS Access database in the web.

I have used ASP to access the web database (in MS Access).
Now i have to upload my local database's data to the web database thru VB code

so Pls tell the following


1.Can i connect to the web database thru VB or do i require to call ASP code
   thru VB?If so how to call ASP code thru VB?

2.How can i transfer records from local to the web based database if the databases are to be connected through - DSN


My thinking went in this way:

1.As of now,im thinking it can be done in ASP.But i dont have any idea
 whether it can be done thru VB(ie. connecting to the web database thru vb).
 

2.For example
  We have two Connections con1 and con2
 Set  con1=New Adodb.connection
 Set  con2=New Adodb.connection
ConnString1="DSN=db;UID=;PWD=;" ' for the local database
ConnString2="DSN=db;UID=;PWD=;" ' for the web database

In my case both are having the same DSN

I though of retrieving current records from the local database using Connstring1 and inserting that into the second Connection statement using execute method.If both the strings are same how can it understand?

I hope im clear in expressing this without confusing you.
For example If i had a DSN-Connection less string
the server's path might differ.Here how can i put that since my DSN name is same for my local machine and for the web also

can i map to the web using system DSN.can i create a new dsn name for that? will it help ?





pls just help me ASAP
thanks
RR

















0
 

Author Comment

by:RajaRajeswari
ID: 9785199
HEy leon

One more question on this
Im trying to transfer data from SQL Server(local) to MS Access(local)
and then from Ms Access(local) to Ms Access(web)

Can i directly transfer data from SQL server(local) to MS Access on the web
if so how to do it ?how should my dsn string for my second connection object be?


pls help me ASAP
RR
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 29

Expert Comment

by:leonstryker
ID: 9788719
>>1.Can i connect to the web database thru VB or do i require to call ASP code
   thru VB?If so how to call ASP code thru VB?


If you can do it with ASP, you can do it with VB.  So the answer is: No you can access it directly from VB.


>>2.How can i transfer records from local to the web based database if the databases are to be connected through - DSN

It does not matter where the database is, in VB once you establish a connection the code does not care.

>>Can i directly transfer data from SQL server(local) to MS Access on the web if so how to do it ?how should my dsn string for my second connection object be?
Yes you can tranfer directly from SQL Server.  Let me see your connection string from Access and we can modify it to work for SQL Server.

Leon
0
 

Author Comment

by:RajaRajeswari
ID: 9795716
Hey leon and all


can i give the connection string like this

MM_con_ecom_STRING ="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\webspace\theservername\db\products.mdb"

or can i give like this

MM_con_ecom_STRING="Driver={Microsoft Access Driver (*.mdb)};User ID=;Password=sa;Data Source=xxx.xxx.xxx.xxx"

where the "xxx" address denotes the ip address of the server say 191.190.189.179(dummy nos)

These are from VB i would be trying to call

But do i have to first register the web server name or ip address in the client network utility and do or i dont know how to connect it .

can you give me any pseudocode if u know please

Please if anyone can help me.Its a bit urgent

Thanks
RR



 
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9796948
Check out this link on for syntax for a connection string of a remote object

http://www.able-consulting.com/MDAC/ADO/Connection/MSRemote.htm

Leon
0
 

Author Comment

by:RajaRajeswari
ID: 9809353
Hai leon
thats  a very good link .thanks for that

We are hosting our site on a server.Those server people are telling that we cant do that remote uploading of records for Ms Access.But for sql server we can do.

I have written the following to the server person as
"
is It not possible to connect from local system(SQL server) to the web database in MsAccess if you can give us the neccessary permission .Please give us the necessary permission,
as our requirement needs us to insert/update records from the local?"


For which the server person responded as

"For SQL Database, yes you may connect from your local to SQL server remotely.For MS Access, you must be a valid user who belongs to the same server where your MS Access is hosted on. "

For which i replied as

"As you see,we are having this domain and web services hosted on your server and we are a registered user of you.Iam not a valid user?.what else i should have to become a valid user?pls just clarify as iam naive about this."

For which He replied as

"Windows 2000 / 2003 servers are using NTFS for file system. Access to a NTFS formatted file system request a valid user with valid permissions either on local server or a active directory domain. Here, what I refer to as "valid user" is a user on the same server, not an the server's site's client.
 
For example, if the domain in question is "xxxx.net", when we setup this domain, 2 users are created on server.
 
a) xxxx.net who is also your FTP user. This user has permission to read, write and modify entire xxxx.net directory.
 
b) xxxx.net_web user will be served as anonymous user for accessing your web contents. xxxx.net_web user was granted with read permission to your "wwwroot" folder and write permission to your "database" folder.
 
You are allowed to write to your MS Access database using your web application or script due to xxxx.net_web user has the right permission to do so and the database reside on the same physical location as your script. And please take note, this xxxx.net_web user is only valid on the same server where your database was hosted on.
 
If you wish to connect remotely to your database (for example from your office), the only way is to use SQL as your database."

Now can u tell me that only by using SQL we can connect to the remote database .
ie do i have to convert my access database into sql database on the web

I tried from my local system using VB to connect to the remote system (from the link you had given)  as follows:

oConn.Open "Provider=MS Remote;" & _
           "Remote Server=http://myServerName;" & _
           "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\webspace\...\mydb.mdb", _
            "", ""
 
But it says Internet Servor Error 8447


pls can you help me as soon as possible as its very urgent

Thanks
RR
 



0
 
LVL 29

Accepted Solution

by:
leonstryker earned 225 total points
ID: 9809893
Basically what they are telling you is this: They are not going to issue you the type of permissions, which you would need to work with Access as your database on their server.  I can't really say that I blame them, since Access is a much less stable product as well as a greater security risk.  You would be much better off going to SQL Server, especially since it sounds that your business is going to rellying on this software.

It may be possible to do what you want to do even with the permission levels you currently have, but I do not have enough knowledge in this area to really help you out.  If you are interested in perusing that question I would suggest asking it in the Networking, ASP, or the IIS areas where people deal with this kind of stuf all the time.

Leon
0
 

Author Comment

by:RajaRajeswari
ID: 9810099
Hey Leon,

Ok thanks.

Thanks a lot.I will try to check those options also.
I will change to SQL Server If it is not working.
Once Again thanks a lot for your effort.Im giving you full points.
RR


0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9811723
RajaRajeswari,

Good luck and thanks for the grade.

Leon
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now