Access -> SQL Server Express via ADO

[stanl]

I have 2 procedures to (1) Create an SQL Server Exp DB (2) SELECT INTO a table from an Access DB into a table in the Server DB - both done with a single SQL statement using an ADODB.Connection Object.

Although I have some concerns with the Connection Objects .state property only supporting Open and Closed, both procedures will work. However, the 2nd is dependent upon the Server configuration to support 'Ad Hoc Remote Queries' [A.K.A. Ad Hoc Distributed Queries].

Question: is there a way to programmatically determine if this [configuration] is enabled and change if not?

Stan

[XLGibbs]

There are configuration properties that can be manipulated. Generally, the procedure is "sp_Configure" and it accepts variables for the configuration setting and the new value of the setting.

I am unfamiliar with SQL Server Express, but you would likely have to be an server admin to change the setting (or at least have admin login creds in your code to execute the procedure).

the T-SQL version would be:

[vba]
exec sp_configure 'Ad Hoc Distributed Queries',1
[/vba]

1 being "on", 0 being "off"

[stanl]

Thanx for the reply. I tried something like that... SQL Express is pretty much the same as SQL Server, especially since I am using ADO and the SQLOLEDB.1 Provider... figured it should be "exec sp_configure 'Ad Hoc Remote Queries',1 ... but got a COM error [attached]. At first I thought that a connection.execute() was not appropriate, but I tried with

exec sp_configure 'remote access',1
exec sp_configure 'show advanced options',1

and both worked. Oh Well Stan

[XLGibbs]

Strange. The line as I provided is exactly as shown from MSDN as well.

YOu might have to reference the "master" db to change the setting or something.

I am having a similar issue with remote query timeouts. the settings are all at 0 (For infinite), but I still get a timeout/server resource error. MSDN is less than helpful as expected on this issue too. And each of the linked servers being used has essentially unlimited resources for what I need to do.

[stanl]

Strange. The line as I provided is exactly as shown from MSDN as well.

Oh Boy! MSDN... weren't they a source for the existence of WMD's?

YOu might have to reference the "master" db to change the setting or something.

Tried that. No Luck

I am having a similar issue with remote query timeouts. the settings are all at 0 (For infinite), but I still get a timeout/server resource error. MSDN is less than helpful as expected on this issue too. And each of the linked servers being used has essentially unlimited resources for what I need to do.

I set connection.CommandTimeOut =0. It has allowed me to SELECT INTO [sql server tables from Access tables] 500-600 meg per .execute() no problems. BTW: I attached how the Remote Queries appear in the Surface Area Configuration Features tool - why the error object reports it doesn't exist or is advanced is beyond me. Stan