Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win



Posted on 2003-11-11
Medium Priority
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
Question by:RajaRajeswari
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
LVL 29

Expert Comment

ID: 9722113

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.


Author Comment

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

Author Comment

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 29

Expert Comment

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


Author Comment

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


Author Comment

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
LVL 29

Expert Comment

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.


Author Comment

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 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


LVL 29

Expert Comment

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



Author Comment

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


LVL 29

Accepted Solution

leonstryker earned 900 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.


Author Comment

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.

LVL 29

Expert Comment

ID: 9811723

Good luck and thanks for the grade.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

597 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