-
Oct 9th, 2012, 04:59 AM
#1
Thread Starter
Member
Copy tables from SQL Server Database to Access Database
hello guys, ive got this problem to figure out
i need to copy all the tables and the content of them from a SQL Server Database to an ACCESS database.
using the "SELECT * INTO <tableName> IN '<ACCESSdbPath>' FROM <tableName>" query from the SQL Server connection doesnt work
using the same query from ACCESS to ACCESS work properly and all the tables are copied into the <ACCESSdbPath> database without any issue...
surfing the web i didnt get whether what im doing is possible or not between sql server and access so i rather know your opinion about this.
thank you very much
-
Oct 9th, 2012, 05:29 AM
#2
Re: Copy tables from SQL Server Database to Access Database
From SQL Server you can't.
But inAccess database you can create (and refresh) a linked table to SQL Server:
ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables
http://support.microsoft.com/kb/275249/en
next the table will listed in database and you can access them as a normal table.
-
Oct 9th, 2012, 06:29 AM
#3
Re: Copy tables from SQL Server Database to Access Database
Be aware however that using this method any changes to the data via Access is actually changing the data in the SQL Server tables.
Are you using VB6 or are you doing this from Access?
-
Oct 9th, 2012, 07:04 AM
#4
Thread Starter
Member
Re: Copy tables from SQL Server Database to Access Database
thank you for the answer gibra
i managed to create a linked table via code but just between ACCESS-ACCESS
i need to do it from an ACCESS connection to a SQL Server table.
Code:
tbl.Properties("Jet OLEDB:Link Datasource") = "C:\myDB.mdb"
tbl.Properties("Jet OLEDB:Remote Table Name") = "tabTagliente"
tbl.Properties("Jet OLEDB:Create Link") = True
i need to figure out how to give the "path" of the SQL Server database i want to connect to. i tried to provide the DSN connection string but it says me "database path not valid"
any suggestions?
Last edited by LiukSPV; Oct 9th, 2012 at 08:51 AM.
-
Oct 9th, 2012, 07:10 AM
#5
Re: Copy tables from SQL Server Database to Access Database
What version of Access and what version of SQL Server?
-
Oct 9th, 2012, 07:24 AM
#6
Thread Starter
Member
Re: Copy tables from SQL Server Database to Access Database
im doing everything in VB6
the problem now is connecting to the SQL server db and not to another access db...
-
Oct 9th, 2012, 07:33 AM
#7
Thread Starter
Member
Re: Copy tables from SQL Server Database to Access Database
by using this: tbl.Properties("Jet OLEDB:Link Provider String") = <DSN string connection> , pops up this error "Impossibile trovare ISAM installabile." (Can't find any installable ISAM)
Last edited by LiukSPV; Oct 9th, 2012 at 07:39 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|