SELECT INTO via OpenRowset

[stanl]

I have an issue where updating from Access to SQL Server might involve duplicate rows on a non-primary key field.

Currently This is performed by an ADO While loop which iterates the Access Table by row. I have tried using an ADO Command with [pseudo-code] OpenRowset

[vba]
SELECT * INTO dbo.SQLTable
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ''C:\Access.mdb'',"SELECT * FROM AccessTable WHERE NOT EXIST ("SELECT * FROM dbo.SQLTable WHERE dbo.SQLTable.Field = AccessTable.Field"))

[/vba]

But nothing happens. I assume there is improper syntax, and if it catches anyones eye, I would appreciate any corrections. Thanks... Stan

[stanl]

my bad the title was openrowset and I used opendatasource in the code. However, I don't believe there is that much difference between the two, and neither works.

[tpoynton]

I ventured into here to learn, so excuse my incorrectly pointing this out of it is wrong!

does there need to be a closing single quote between the two closing parentheses? (closes quote at 'data source...)

[Tommy]

Stan,

I think I saw ' ' in the connection string I'm pretty sure it is a typo here.

but .....
[VBA]"'SELECT * INTO dbo.SQLTable FROM OPENDATASOURCE" & _
"( 'Microsoft.Jet.OLEDB.4.0', 'Data Source = C:\Access.mdb'," & _
"'SELECT * FROM AccessTable WHERE NOT EXIST (SELECT * FROM dbo.SQLTable WHERE dbo.SQLTable.Field = AccessTable.Field)')'"[/VBA]

[stanl]

Stan,

I think I saw ' ' in the connection string I'm pretty sure it is a typo here.

I think each parameter has to be enclosed in single/double quotes, for example


[vba]
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS o
ON c.CustomerID = o.CustomerID
[/vba]

otherwise I would get a syntax error. I have another realtime test for OpenRowset(), and perhaps this thread can be re-visited.

Stan