Import to Access from SQL Server

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Hi,

I must say I have never automated it. I usually do this for a specific, non-reoccurring task, so never had the need to automate it.
Can you tell me other details about what you need to do? Will you be connectng to the same SQL Server database, importing the same table?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote