change DAO code to ADO

[franco]

Hello All,

I have a question, I have a function which can let me use SQL in VBA
Excel to to query with Access.

Set wrkODBC = CreateWorkspace("ODBCWorkspace", "research", "",_
dbUseODBC)
Set cn = wrkODBC.OpenConnection("", dbDriverNoPro,_ False,
"ODBC;DSN=<ABCDE>;UID=hello;PWD=hello")

this works. but i want to use ADO method, so I change it into

Dim getAccess As ADODB.Connection
sConnect = "Provider=SQLOLEDB;" & "Initial Catalog=Goldfinger;" _ &
"User ID = research;" & "Password = research;"
getAccess.ConnectionString = sConnect
getAccess.Open

i dont know why, i always got a error message telling me: 91 Object
variable or With block variable not set -2147221504

or if I want to run:

Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenKeyset, adLockReadOnly, adCmdText

then it will tell me: -2147467259 [DBNETLIB][ConnectionOpen
(Connect()).]SQL Server does not exist or access denied. -2147221504


Can any one help me? Thanks a lot.

 

[Simon Murphy]

try
set getAccess = new ADODB.Connection

before you use it
or Dim it with New

cheers
Simon