Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Copy a Sql Server Table to Access Using VBA

Posted on 2005-03-29
Medium Priority
Last Modified: 2008-01-09
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?

Question by:carpetflyer
  • 4
  • 3
LVL 12

Accepted Solution

Paurths earned 1500 total points
ID: 13657693
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"

Author Comment

ID: 13657857
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.

LVL 15

Expert Comment

by:Simon Ball
ID: 13660289
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?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 13664036
Ok i figured out how to use ODBC connection using the following connection string for SQL Server:
"ODBC;Driver={SQL Server};Server=xxx.xxx.xxx.xxx;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?

LVL 15

Expert Comment

by:Simon Ball
ID: 13665363
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...
LVL 15

Expert Comment

by:Simon Ball
ID: 13665451
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..

Author Comment

ID: 13665523
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.
LVL 15

Expert Comment

by:Simon Ball
ID: 13669617
nice work.  i did not realise you were doing this "remotely" from one db to another.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question