Converting Access SQL to SQL Server

[jharding08]

I have to do a data transfer between two databases. The code was already written to move data between two access MDB files. I am changing the backend database to SQL Server, which inevitably will change the SQL statement. here is the statement for two MDB's -

"INSERT INTO Packages " & _
"SELECT * FROM [MS Access;DATABASE=" & DatabaseFrom & ";].Packages MSP WHERE " & _
" MSP.PackageID NOT IN (SELECT PackageID FROM Packages) AND MSP.PackageID NOT IN (SELECT PackageID FROM Deleted)"


I know it involves using OPENROWSET

 

[SQLSister]

Are the databases on the same server? YOu can specify the database in the SQL statement without using OPENROWSET. Or is one still an Access database?

Incidentally delete is a very bad name for a table. It is one fo SQL server's reserved words. YOu will need to enclose it in brackets [DELETE] everytime you use it. and given what the reserved word does, a script error on your part could have bad consequestes.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jharding08]

one is still an access database, and the other is SQL server. can i use "Deleted" as a table name?

 

[SQLSister]

SQl Server uses deleted within triggers as the name of psuedotable it stores the deleted records in for the trigger to process. I would not recommend using it on SQL server at all, but if you have to just make sure you always put brackets around it. If ou can I rename it something like DeletedRecords. I realize that if a lot of code hits this table that could be a problem.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jharding08]

This is already an already completed application, so changing it here would also constitute changing it in many other locations. I can just put brackets around it.

 

[jharding08]

what is wrong with this conversion?

FROM-

sSQL = "INSERT INTO Packages " & _
"SELECT * FROM [MS Access;DATABASE=" & DatabaseFrom & ";].Packages MSP WHERE " & _
" MSP.PackageID NOT IN (SELECT PackageID FROM Packages) AND MSP.PackageID NOT IN (SELECT PackageID FROM Deleted)"

TO-

sSQL = "INSERT INTO Packages " & _
"SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','" & DatabaseFrom & "';;, Packages ) MSP WHERE " & _
" MSP.PackageID NOT IN (SELECT PackageID FROM Packages) AND MSP.PackageID NOT IN (SELECT PackageID FROM Deleted)