Results 1 to 7 of 7

Thread: Copy tables from SQL Server Database to Access Database

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    35

    Question 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

  2. #2
    gibra
    Guest

    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.

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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?

  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    35

    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.

  5. #5
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Copy tables from SQL Server Database to Access Database

    What version of Access and what version of SQL Server?

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    35

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

  7. #7

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    35

    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
  •  



Click Here to Expand Forum to Full Width