Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Import to Access from SQL Server

    Hi Guys.

    Really Mega stumped on this one.

    I can download a table line by line after using a connection string:

    Set oConn123 = New ADODB.Connection
    oConn123.Open "Driver={SQL Server};" & _
    "Server=;" & _
    "Address=;" & _
    "Network=;" & _
    "Database=;" & _
    "Uid=;" & _
    "Pwd=;"

    And calling it via:

    Set rs123 = New ADODB.Recordset
    rs123.Open "Select * From DB_1", oConn123


    And then:

    Do
    ' Extracting individual fields and then appending the line.
    Loop While rs123.EOF = False


    Which takes long time.

    Is it better/Quicker to download the whole table creating a new table in Access 2007, If so:

    Hows it done, I am assuming its an SQL string, But I cant seem to get the Downloaded Table to apply itself to the Access table (CurrentDB.) where the active code is.

    Be really grateful if there's any illumination out there.

    Graeme

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,172
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    What I usually do is to create an ODBC data source for the SQL Server database (details depend on your OS). Once you complete this ODBC data source creation, you simply import the table from the ODBC data source. Again, details depend on the Access version you are using. Currently I use Access 2010, so on the External Data tab, I simply choose ODBC database, choose the option to import the table instead of simply linking to it, and Access lets me choose the table I want.
    It will be slightly different in previous versions, especially when specifying the external source, but you will have the possibility of specifying an ODBC data source and from then on it will be the same.

    This is clearly the easiest and quickest way.

  4. The Following User Says Thank You to ruirib For This Useful Post:

    graliv1 (2011-07-25)

  5. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    Excellent Yes So I've set this up successfully from Administrative tools -> Data Source(ODBC) and called it DB1

    Sorry This will save me some scurrying around, do you have a sample connection string, so I can adapt it.

    I've managed to Download the table: SAMPLE1 using a DSN called CT1 On my desktop. So I'm connected.

    I'm looking automate this for minimum handling.

    Thank-You

  6. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,172
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    Hi,

    I must say I have never automated it. I usually do this for a specific, non re-occurring task, so never had the need to automate it.
    Can you tell me other details about what you need to do? Will you be connecting to the same SQL Server database, importing the same table?
    Last edited by ruirib; 2011-07-25 at 10:41. Reason: typo

  7. The Following User Says Thank You to ruirib For This Useful Post:

    graliv1 (2011-07-25)

  8. #5
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Excellent I've managed to Create a linked file (which is les crude than what I was Planning), I'll have to play with it a bit as it could cause a major revolution, in the way I Code.
    Hopefully I can make this work

    With Gratitude

    Graeme

  9. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,172
    Thanks
    129
    Thanked 1,139 Times in 1,050 Posts
    You're welcome, Graeme.

    If what you plan to do is simply to repeat the import procedure without any changes, you can save that import procedure and execute from a macro or VBA code. Anything else, you will have to describe what you need to do.

    Regards

    Rui

  10. #7
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You might want to Consider this as an alternative:
    1) Using Code, create a linked table to the SQL Server database (see: http://support.microsoft.com/kb/892490)
    2) Once you have that linked table on your database it is a simple matter to Execute a Make Table Query off that table to create a local table in your Access database with everything you need.
    3) Once you are done with the linked table, go ahead and delete it

    I would think this would be MUCH faster than open recordsets and looping through records, etc.

    Hope that helps
    Bob Oxford
    Software Wizards, Inc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •