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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 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