PDA

View Full Version : SELECT INTO via OpenRowset



stanl
03-23-2008, 06:14 AM
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


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"))



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

stanl
03-24-2008, 04:43 AM
my bad:banghead: 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
04-20-2008, 06:53 PM
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
04-21-2008, 10:16 AM
:hi: Stan,

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

but .....
"'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)')'"

stanl
04-25-2008, 04:39 AM
:hi: Stan,

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


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



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


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

Stan