Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Statement From Access To SQL Server

Status
Not open for further replies.

jharding08

Programmer
Dec 20, 2005
30
US
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 -

"UPDATE Packages P INNER JOIN " & _
"[MS Access;DATABASE=" & DatabaseFrom & ";].Packages MSP " & _
"ON MSP.PackageID = P.PackageID SET " & _
"P.TrackingNo = MSP.TrackingNo, " & _
"P.RecipientID = MSP.RecipientID, " & _
"P.PackageTypeID = MSP.PackageTypeID, " & _
"P.LocationID = MSP.LocationID, " & _
"P.CarrierID = MSP.CarrierID, " & _
"P.MailRoomID = MSP.MailRoomID, " & _
"P.ReceivedUserID = MSP.ReceivedUserID, " & _
"P.ReceivedDate = MSP.ReceivedDate, " & _
"P.DeliveredUserID = MSP.DeliveredUserID, " & _
"P.DeliveredDate = MSP.DeliveredDate, " & _
"P.DeliveryStatusID = MSP.DeliveryStatusID, " & _
"P.RecipientSignatureID = MSP.RecipientSignatureID, " & _
"P.Comments = MSP.Comments, " & _
"P.LastUpdated = NOW() " & _
"WHERE P.LastUpdated<>MSP.LastUpdated AND P.LastUpdated<MSP.LastUpdated"


When i run the execute with DATABASEFROM being the path to a UDL file pointing to the SQL Server Database, I get the error : "Syntax Error Near P". Any SQL Server experts out there?
 
If I understand correctly, you wanna update SQL Server table with data from Access DB?

UPDATE/JOIN syntax is different, NOW() must be replaced with GETDATE(), the rest can be done with OPENROWSET()... try something like:
Code:
UPDATE P
SET TrackingNo = MSP.TrackingNo,
	RecipientID = MSP.RecipientID,
	PackageTypeID = MSP.PackageTypeID,
	LocationID = MSP.LocationID,
	CarrierID = MSP.CarrierID,
	MailRoomID = MSP.MailRoomID,
	ReceivedUserID = MSP.ReceivedUserID,
	ReceivedDate = MSP.ReceivedDate,
	DeliveredUserID = MSP.DeliveredUserID,
	DeliveredDate = MSP.DeliveredDate,
	DeliveryStatusID = MSP.DeliveryStatusID,
	RecipientSignatureID = MSP.RecipientSignatureID,
	Comments = MSP.Comments,
	LastUpdated = GETDATE()
FROM Packages P 
INNER JOIN
OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', <connection stuff here>, Packages ) MSP
ON MSP.PackageID = P.PackageID 
WHERE P.LastUpdated<>MSP.LastUpdated AND P.LastUpdated<MSP.LastUpdated
Not tested of course - see documentation for OPENROWSET() in Books Online.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
I get an error: "OLE/DB Provider: Could Not Find An Installable ISAM" any ideas?
 
Very probably incorrect connection arguments. This works for me (blah.mdb is not password-protected):

OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'f:\temp\blah.mdb';;, Packages ) MSP

Without ;; things crash exactly as you described

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
i tried this:
"Update P " & _
"SET TrackingNo = MSP.TrackingNo, " & _
"RecipientID = MSP.RecipientID, " & _
"PackageTypeID = MSP.PackageTypeID, " & _
"LocationID = MSP.LocationID, " & _
"CarrierID = MSP.CarrierID, " & _
"MailRoomID = MSP.MailRoomID, " & _
"ReceivedUserID = MSP.ReceivedUserID, " & _
"ReceivedDate = MSP.ReceivedDate, " & _
"DeliveredUserID = MSP.DeliveredUserID, " & _
"DeliveredDate = MSP.DeliveredDate, " & _
"DeliveryStatusID = MSP.DeliveryStatusID, " & _
"RecipientSignatureID = SP.RecipientSignatureID, " & _
"Comments = MSP.Comments, " & _
"LastUpdated = GETDATE() " & _
"FROM Packages P INNER JOIN " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\Program Files\IntelliTrack\Package Track\Transfer12202005152855.mdb';;, Packages ) MSP " & _
"ON MSP.PackageID = P.PackageID " & _
"Where P.LastUpdated <> MSP.LastUpdated And P.LastUpdated < MSP.LastUpdated"


it said the path C:\Program Files\IntelliTrack\Package Track\Transfer12202005152855.mdb was invalid, but i know i have a mdb named that there
 
I copy/pasted your code to notepad. There are 4 spaces between Program and Files. I suspect there should only be 1 space.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
its only one space, it might have gotten messed up in the formatting...
 
OK, it was just a thought.... And here's another one...

When you reference files in SQL Server, they are actually referring to the computer that SQL Server is installed on.

For example, if your sql server is installed on a machine named SQLServer, then you are actually trying to refer to the file \\SQLServer\c\Program Files\etc...

So, does this mdb file exist on the same computer running SQL Server?

Additionally, the login account used for SQL Server must have permissions to the folder and file that you are using.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
everything is local on my work computer. it is a mdb file that is just created before i run this sql, so no user or password. thank you for the ideas tho, i will try them anyway
 
Just playing around a little, I noticed that I need quotes with xp_cmdshell

master..xp_cmdshell 'dir C:\Program Files\*.*'

gives me an error, but...

master..xp_cmdshell '"dir C:\Program Files\*.*"'

Works just fine. So, try changing your code to...

Code:
"Update P " & _
   "SET TrackingNo = MSP.TrackingNo, " & _
        "RecipientID = MSP.RecipientID, " & _
        "PackageTypeID = MSP.PackageTypeID, " & _
        "LocationID = MSP.LocationID, " & _
        "CarrierID = MSP.CarrierID, " & _
        "MailRoomID = MSP.MailRoomID, " & _
        "ReceivedUserID = MSP.ReceivedUserID, " & _
        "ReceivedDate = MSP.ReceivedDate, " & _
        "DeliveredUserID = MSP.DeliveredUserID,   " & _
        "DeliveredDate = MSP.DeliveredDate, " & _
        "DeliveryStatusID = MSP.DeliveryStatusID, " & _
        "RecipientSignatureID = SP.RecipientSignatureID, " & _
        "Comments = MSP.Comments, " & _
        "LastUpdated = GETDATE() " & _
        "FROM Packages P INNER JOIN " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0','[red]""[/red]C:\Program    Files\IntelliTrack\Package Track\Transfer12202005152855.mdb[red]""[/red]';;, Packages ) MSP " & _
        "ON MSP.PackageID = P.PackageID " & _
        "Where P.LastUpdated <> MSP.LastUpdated And P.LastUpdated < MSP.LastUpdated"

You need 2 quotes because you are building this string within a language that already uses quotes as string delimiters.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
what if i was to use a string variable that held the database path
 
This looks like VB, so here's how I would do it...

Code:
Dim cFile As String
Dim cSQL As String

cFile = "C:\Program Files\IntelliTrack\Package Track\Transfer12202005152855.mdb"

cSQL = "Update P " & _
   "SET TrackingNo = MSP.TrackingNo, " & _
        "RecipientID = MSP.RecipientID, " & _
        "PackageTypeID = MSP.PackageTypeID, " & _
        "LocationID = MSP.LocationID, " & _
        "CarrierID = MSP.CarrierID, " & _
        "MailRoomID = MSP.MailRoomID, " & _
        "ReceivedUserID = MSP.ReceivedUserID, " & _
        "ReceivedDate = MSP.ReceivedDate, " & _
        "DeliveredUserID = MSP.DeliveredUserID,   " & _
        "DeliveredDate = MSP.DeliveredDate, " & _
        "DeliveryStatusID = MSP.DeliveryStatusID, " & _
        "RecipientSignatureID = SP.RecipientSignatureID, " & _
        "Comments = MSP.Comments, " & _
        "LastUpdated = GETDATE() " & _
        "FROM Packages P INNER JOIN " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0','[red]""" & cFile & """[/red]';;, Packages ) MSP " & _
        "ON MSP.PackageID = P.PackageID " & _
        "Where P.LastUpdated <> MSP.LastUpdated And P.LastUpdated < MSP.LastUpdated"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Yes, double quotes are not necessary in OPENROWSET() - and not allowed in OS path/filename anyway.

Can you post VB code? Or do ?/response.write cSQL?



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
This is the latest...

'create sql string
sSQL = "Update P " & _
"SET TrackingNo = MSP.TrackingNo, " & _
"RecipientID = MSP.RecipientID, " & _
"PackageTypeID = MSP.PackageTypeID, " & _
"LocationID = MSP.LocationID, " & _
"CarrierID = MSP.CarrierID, " & _
"MailRoomID = MSP.MailRoomID, " & _
"ReceivedUserID = MSP.ReceivedUserID, " & _
"ReceivedDate = MSP.ReceivedDate, " & _
"DeliveredUserID = MSP.DeliveredUserID, " & _
"DeliveredDate = MSP.DeliveredDate, " & _
"DeliveryStatusID = MSP.DeliveryStatusID, " & _
"RecipientSignatureID = MSP.RecipientSignatureID, " & _
"Comments = MSP.Comments, " & _
"LastUpdated = GETDATE() " & _
"FROM Packages P INNER JOIN " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0','" & DatabaseFrom & "';;, Packages ) MSP " & _
"ON MSP.PackageID = P.PackageID " & _
"Where P.LastUpdated <> MSP.LastUpdated And P.LastUpdated < MSP.LastUpdated"


'execute the query
oCn.Execute sSQL
 
OK, can you access .MDB data from Query analyzer:
Code:
SELECT MSP.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','hardcoded_mdb_filepath_here';;, Packages ) MSP
?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Looks like database side works OK.

And variable DatabaseFrom holds perfectly valid and correct filepath (use debugger/Response.write/eyes :) to check that)?

Another question: what kind of provider are you using to connect on SQL Server?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
i am opening the connection using a UDL (FILENAME="C:\...)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top