Copy a Sql Server Table to Access Using VBA

Hello everyone.

I have been researching a way to import a table from a SQL Server to an access table without using any wizards or DTS.  Every day we have to update surveys from our website to our in house access database.  So the survey results are stored on a SQL Server which is located at our web hosting company.

So far I have created a stored procedure that returns a temporary table.  I need to somehow copy that temp table to an Access table.  I want to avoid using Import features because I want a non-tech user to click a button which will handle the import process.

Can it be done using ADO? I couldnt imagine looping through 50,000 records and copying each field to an Access table. That sounds like a nightmare!

Also, I have heard DTS could schedule stuff but it sounds really hard. I am new at all of this.

Any ideas?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can this help:

DoCmd.TransferDatabase acImport, "ODBC Database", _
        "ODBC;DSN=DSNName;UID=xxx;PWD=xxx;LANGUAGE=us_english;" _
        & "DATABASE=DBName", acTable, "tbl_yourtable", "tbl_newTableName"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carpetflyerAuthor Commented:
Hi Paurths.

I dont have a DSN, i have only have a OLE DB connection using an IP address. Is it still possible? I tried what you have me and it wouldnt work. I dont know what the database type would be for an OLE DB connection.

Simon BallCommented:
can you make a link to the table and then use a query t copy the data from the linked table to the local database?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

carpetflyerAuthor Commented:
Ok i figured out how to use ODBC connection using the following connection string for SQL Server:
"ODBC;Driver={SQL Server};;Database=DBName;Uid=user;Pwd=password"

Sudonim: I will try your idea out. The problem is I can transfer the table from the SQL Server to the local database but I wish TransferDatabase could replace the existing table.  The table is actually a link to our backend Access DB and it will not replace the existing table. It will create a new table, so if the table "TABLE" exists in the database, then when I use TransferDatabase, "TABLE1" will be created instead of replacing "TABLE"

I think I will have to delete the table on the backend, copy the table over, then recreate the link from the frontend to the backend db. Do you exports have an easier solution?

Simon BallCommented:
to delete a table, and ignore if its not there

(in to a function to make the error code easier to pick up):

public function del_my_table (tblname as string)
On Error GoTo Error_trap

DoCmd.DeleteObject acTable, tblname


Select Case Err.Number
Case 0
Case "7874"
'table is missing, cannot be deleted
Case Else
MsgBox Err.Number
End Select
end function

then in your main code call it using this line of code:

del_my_table ("table")

put this in before you import the data from the server

you could of course rename "table"

using docmd.rename, then import the table, append the data, delete the imported table, then rename the linked table back to its old name...
Simon BallCommented:
you might need to docmd.setwarnings flase before the docmd.deleteobject or .rename parts.

and set it back to true after the code has finished its work..
carpetflyerAuthor Commented:
Hi Sudonim. Thanks for replying.

I figured out what to do.

The problem was I had to have the SQL Server Table import to my backend database. So since there is already a link from the front end to the backend, i would leave that alone.

So I did

Dim app As New Access.Application

app.OpenCurrentDatabase backendlocation
If doesTableExist(app, strTableDest) Then
        app.DoCmd.DeleteObject acTable, strTableDest
End If

app.DoCmd.TransferDatabase acImport, "ODBC Database", STR_SQLSERVER_ODBC_CONN, acTable, strTableSource, strTableDest
Set app  = Nothing

So the "TransferDatabase" method is being called on the backend database not the frontend.  This will create the new imported table on the backend, while the link will still exist from the front end to the backend.

My doesTableExist is similar to yours but it looks through the backend's tabledefs for a match. (Came across this code on EE).

You have some good ideas with yours, by letting it trap the errors which will save time by not looping through x amount of tables.

Thanks for the input.
Simon BallCommented:
nice work.  i did not realise you were doing this "remotely" from one db to another.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.