dcsimg
Results 1 to 7 of 7

Thread: Copy tables from SQL Server Database to Access Database

Hybrid View

  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
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,668

    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

    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.

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,995

    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?

  5. #5

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

  6. #6
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Copy tables from SQL Server Database to Access Database

    What version of Access and what version of SQL Server?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  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
  •  



Featured


Click Here to Expand Forum to Full Width