Sending an SQLServer TSQL command from VBA

[Waltang]

Hello,

I have run into a minor annoying problem. First a little background. I am currently redesigning my companies database application. The original application was done entirely in Access 97 and replicated across multiple sites. It is also used seperately by different divisions of the company.

I used the DTS to convert the original Access 97 Backend database to a MS SQLserver 2k Database, and made a number of changes to the structures of the tables. Some of the original tables were using text fields as primarykeys with related tables based on these records. I have added Identity fields to all of the tables that previously did not have them.

The old database is still currently being used. After making a number of changes to the Frontend database, I think that I have it ready to start testing on a large scale. My boss would like to Put the new database through approx. 4 weeks of rigorous testing. The idea is to have a routine built that can be run about everyother night, to completely repopulate the new SQL DB with all of the data from the current Access 97 database.

My original plan was to when necessary, select all of the tables in SQL, and Generate a Script, then detach and move the database for backup, then run the script to rebuild the database with no data. I also made a new Access database and linked to both the existing Access Tables, and the new SQL tables, then built append queries for every table to insert the Existing data into the SQL databases. I built a table listing the querynames and storing a number that I could use to dictate the order in which the queries were run, then built a module with a procedure to run the queries based on the order from that table.

Everthing works great except for the tables that already had Identity (Autonumber) fields in access will not insert into the SQL tables. It seems that I need to pass the command SET IDENTITY_INSERT ON at the beginning of each query so that it can put values into the Identity fields. I have to retain the current Identy values because the relationships are built on them.

All this to the question, Is there anyway to pass this command from VBA, or did I miss the easier way to accomplish this?

Thank you for taking the time to read this.

-Walt

 

[FoFa]

Have you tried the execute command of your database object? If the connection is static for the whole process it should remain in effect. You also have to turn it off before turning it on for another table or you will get an error.

 

[Waltang]

I'm not sure I understand where your suggesting or exactly what your suggesting I try.

I was under the impression that IDENTITY_INSERT was at the table level, not the database level.

I'm willing to try anything, but not having luck with any form of syntax to what you are describing.

Here is the module I am executing to run the queries. Could you please indicate what you are suggesting that I try, and where.

__________-

Sub DATA_APPEND()

On Error GoTo APPEND_ERROR

Dim STARTTIME As Date
Dim FINISHTIME As Date

STARTTIME = Now()

Dim DBCURRENT As Database
Dim QDFQUERIES As QueryDef
Dim QDFEXECUTE As QueryDef
Dim RCSQUERIES As Recordset

Set DBCURRENT = CurrentDb()
Set QDFQUERIES = DBCURRENT.CreateQueryDef("", "SELECT * FROM [_Query_Run_Order] ORDER BY [_Query_Run_Order].ORDER")
Set RCSQUERIES = QDFQUERIES.OpenRecordset()

Do While Not RCSQUERIES.EOF
Debug.Print "Begining Execution of <<< " & RCSQUERIES.Fields("Query name") & " >>> ** Order # (" & RCSQUERIES.Fields("order") & ") ** " & Now()

Set QDFEXECUTE = DBCURRENT.QueryDefs(RCSQUERIES.Fields("QUERY NAME"))
QDFEXECUTE.Execute

RCSQUERIES.MoveNext
Loop

FINISHTIME = Now()

MsgBox ("Datapopulation ran from " & STARTTIME & " until " & FINISHTIME & ".")

Exit Sub
APPEND_ERROR:
If Err.Number <> 0 Then
Debug.Print "Encountered the following error while executing <<< " & RCSQUERIES.Fields("Query name") & " >>> ** Order # (" & RCSQUERIES.Fields("order") & ") ** ----> " & Err.Number & "(" & Err.Description & ")"
Resume Next
End If

End Sub

_____________

Thank you