How to append records from Access database to MS-SQL database

[scheung]

How do I copy records from an Access (2000) database table to a MS-SQL (2005) table using VBA code?

The tables in my Access and MS-SQL databases already exist and have identical table structures. I need to copy all the records from my Access database table to the SQL database table.

I'm using ADO to connect to my SQL database table and can do this successfully.

I am new to VBA programming and all help appreciated!!


Many Thanks,

SC

 

[PHV]

Why not simply use an append query to a linked table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scheung]

Thank you for replying to my post.

The table in my Access database is temporary and is created just before I need to copy the records into the SQL database table (and then subsequently deleted afterwards by the program).

Can I still use an append query to a linked table?

 

[scheung]

I got a bit further since my last post but still can figure out what I'm doing wrong.

Here is my VBA code so far:


Sub CopyDataToSQL()

Dim strAccessTableName As String
Dim strSQLTableName As String
Dim strSQL As String

strAccessTableName = "[tblHealthAuthority]"
strSQLTableName = "[Structure_Health Authority]"

Dim conSQL As New ADODB.Connection
Dim cmdSQL As New ADODB.Command
Dim rstSQL As New ADODB.Recordset

Dim strConnection As String

' Connect to SQL Database
' Using SQL Server Authentication
strConnection = "Provider=SQLOLEDB.1;Data Source=ServerName;Initial Catalog=SQLDatabase;Network=DBMSSOCN;" & _
"User Id=UserName;Password=xxxxxx;" & _
"Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;"
Set conSQL = New ADODB.Connection
conSQL.Open strConnection

' Set up recordset for SQL table
Set cmdSQL.ActiveConnection = conSQL
cmdSQL.CommandType = adCmdText
cmdSQL.CommandText = "INSERT INTO " & strSQLTableName & " FROM " & strAccessTableName & " IN " & "'C:\DATA\NHS Data\NHSImport.mdb';"
Set rstSQL = cmdSQL.Execute

' Close SQL Connection
rstSQL.Close
conSQL.Close
Set rstSQL = Nothing
Set cmdSQL = Nothing
Set conSQL = Nothing

End Sub


Running the above code results in a run time error occurs stating 'Incorrect syntax near the keyword FROM' on the 'Set rstSQL = cmdSQL.Execute' line.
Thanks in advance,

SC

 

[Craig0201]

I'm really rather scared by this.

Not only do you not have fairly rudimentary skills and are working on important data, you're storing it on a local drive.

'C:\DATA\NHS Data\NHSImport.mdb';"

Blimey.

Data risk tastic.

All I can say is, I hope that you're not working for my local trusts.

C

 

[scheung]

Thanks for your useful comments.

I am working with an offline test Access database - which is why its on my local drive.

SC

 

[Craig0201]

Which is the security risk.

C

 

[scheung]

Look, understand your concern but I have a problem and I would like to focus on the original question please?

Can you help with my original query?
SC

 

[Craig0201]

No. What you are doing is dangerous to the data security of the general population of the UK. Have the data security rules issued by the Cabinet Office not reached you?

You really shouldn't be doing this. Find some other way.

C

 

[scheung]

With respect, I dont undestand your objections. You have no idea of the 'context' in which I am asking for help nor the sensitivity of data (in this example, a list of health authorities and addresses - nothing more)

I am dissappointed with the hostile response to my posts and I will find some other avenue of help instead.

Sorry for any offense caused.

SC

 

[Craig0201]

I am sorry you are disappointed. It is not my intent to cause offence. I am though highly passionate about the security of my and others personal data and where such things as health authority flows, either sensitive commercial information or worse personal information tends to follow.

As such, before going off half cock as I did, I should have asked this first.

Are you holding, will you be holding or do you intend to hold any form of personal or sensitive commercial data including abstracted identifiers such as NHS number on your local drive?

If you the answer to any of the above is true, you really must take another route.

If the answer to all of the above is no, I apologise and will do my best to assist.

C

 

[PHV]

Provided you've defined a linked table I don't see why you use a recordset ...
Tip: have a look at the DoCmd.RunSQL method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886