Previously I wrote some code for an Access 2003 application to verify
on startup that it is correctly linked to SQL Server. Since it was
im****tant that the driver not prompt the user and the rest of the
application uses DAO, I used the following code
Set wksp = DBEngine.CreateWorkspace("", "", "", dbUseODBCCursor)
Set db = wksp.OpenDatabase("", dbDriverNoPrompt, True, strTemp)
However Access 2007 does not sup****t this method, so I rewrote the
code using ADODB as follows:
strTemp = "Provider=SQLOLEDB.1;Data Source=MyServer;Initial
Catalog=Database;Integrated Security=SSPI"
Set conn = CreateObject("ADODB.Connection")
'conn.Properties("Prompt") = 4
conn.Open strTemp
The code runs fine as long as I don't attempt to set the property for
prompt to the value for adPromptNever (4). (I'm using late binding
because the rest to the application uses DAO). But as soon as I
attempt to set the property I get an error message "Object invalid or
no longer set"
Anyone have any thoughts on this? 99% of the time it would not be an
issue since the application will be correctly link, but if on the
first startup after install it cannot find the SQL Server, I don't
want the default prompt to appear but want to handle the problem using
a custom form.
Thanks in advance
Cheryl
|
