I have established a system DSN Data source named 'work' connect to local SQL Server 2000 and the connection test was sucessful.
I also have written the VB 6.0 code below to connect local Microsoft Access:
dim dbLocal as ADODB.Connection
set dbLocal = New ADODB.Connection dbLocal.Open "Provider='Microsoft.Jet.OLEDB.4.0';data source='C:\localdb\data\localdb.mdb'"
I have some store procedures. some of them accessed the local Microsoft Access tables, some of them accessed both local Microsoft Access tables and local SQL Server tables.
When I tried to execute the store procedures only accessed tthe local Access tables, they passed.
When I tried to execute the store procedures that linked to SQL Server tables, the error is:
ODBC--connection to work failed.