Not sure I'll get an answer

[stanl]

but... given SQL

[vba]
cSQL = "SELECT * INTO [dbo].[Table] FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=" & cMDB & "')...[Table]"

oConn.Execute(cSQL,,adExecuteNoRecords)
[/vba]
when run from a remote machine to a box with an SQL Server gives the infamous "DataBase is used exclusively by another user"

but if excuted remotely with the mdb on the same box as SQL Server works fine. Lots of reports of this error with the standard blame the victim crap... but NO

1. the mdb is not in use by another
2. no phantom .ldb files present
3. UNC shares set properly
4. ADHOC queries enabled in SQL Server

..if it is a bug in Jet 4.0 maybe I haven't googled deep enough.

[Tommy]

Hey stanl,

I am guessing here, and with no connection string for the SQL server, so with that in mind.

Your cMDB has "C:\"..... instead of "\\Mycomputer\C:\".....
I guess I am saying that the cMDB is not fully qualified.

Local share is not the same as server sharename, would be another guess.

I'm still learning the .NET and SQL Server 2005 stuff so I could not even be in the same ball park.

[stanl]

Hey stanl,

I am guessing here, and with no connection string for the SQL server, so with that in mind.

Your cMDB has "C:\"..... instead of "\\Mycomputer\C:\".....
I guess I am saying that the cMDB is not fully qualified.

No, like I said a fully qualifield path - tried it with both Windows authentication and SQL Server login/pw.

Local share is not the same as server sharename, would be another guess.

My first guess also. However, SELECTS and INSERTS work from the connection, so permissions are not at issue; I'm just assuming it is a bug in the OpenDataSourc() method, from remote machines.

[XLGibbs]

My first guess also. However, SELECTS and INSERTS work from the connection, so permissions are not at issue; I'm just assuming it is a bug in the OpenDataSourc() method, from remote machines.

It is far better to qualify the required SELECT * INTO by breaking it into a
Create Table / Insert Into method.

If the database has any open transactions (without needing a .ldb or other phantom lock)...and this includes a simple "connection". By making the connection, you are in fact locking the database at running time (in some cases, I think). Since Insert Into works, and Select works given your statement, I would change the SELECT INTO, to the INSERT INTO () SELECT FROM statement.

[stanl]

It is far better to qualify the required SELECT * INTO by breaking it into a
Create Table / Insert Into method.

Agreed; and perhaps I should have made my intentions clearer. I was really testing data conversions and how SQL Server handles field lengths - for example an Access char field that contains "12/07/2007 AM" should [and does] fail as a date/time in SQL Server. I am able to see this by manually going in under tasks/import, but was hoping SELECT INTO... would provide the same info.

I did however verify from another source that OpenDataSource() is buggy with respect to SELECT INTO.

[Tommy]

Well FWIW the documentation says to include the password and UID in all connection strings.

"All connection information, including passwords, must be provided each time OPENDATASOURCE is called."

obtained from : http://msdn2.microsoft.com/en-us/lib...5(SQL.80).aspx

It is far better to qualify the required SELECT * INTO by breaking it into a
Create Table / Insert Into method.

I also agree FWIW.
I tend to over simplify just because I will have to figure out why it didn't work 5 years from now. By then I have either advanced in my learning enough to spot it immediatly, or I will not have to break my head trying to figure out what is going on before I can find and fix the issue. Today it maybe Fred had a hangover and fat fingers, or it could be I have to deal with metric measurements in lieu of imperial. Either way it is broken out simply for maintianablity.