Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open recordset using ODBC
The following code connects to an existing data source using and existing
ODBC connection when used in MS Access. I am attempting to make the same connection from Excel, with one exception. When used in an Access enviornment, the ODBC connection is used to link the table. I can then use a different statement to actullay open the recordset. I am getting an error at the "rstRecordset.Open" statement. Here is the code: Dim cnnConnect As ADODB.Connection Dim rstRecordset As ADODB.Recordset Set cnnConnect = New ADODB.Connection cnnConnect = "ODBC;" cnnConnect = cnnConnect & "DSN=ACCOUNTING;" cnnConnect = cnnConnect & "APP=Microsoft Excel;" cnnConnect = cnnConnect & "DATABASE=PHAGF;" cnnConnect = cnnConnect & "UID=ACCTNG;" cnnConnect = cnnConnect & "PWD=dtatrf;" cnnConnect = cnnConnect & "TABLE=BPCSF_GPM" Set rstRecordset = New ADODB.Recordset strSql = "SELECT Max(BPCSF_GPM.PYEAR) AS MaxOfPYEAR FROM BPCSF_GPM " _ & "WHERE (((BPCSF_GPM.POPNCL)=""Y"") AND ((BPCSF_GPM.PAROFF)<21));" rstRecordset.Open _ Source:=strSql, _ ActiveConnection:=cnnConnect, _ CursorType:=adOpenDynamic, _ LockType:=adLockReadOnly, _ Options:=adCmdText varMaxYear = rstRecordset.Fields("MaxOfPYEAR").Value rstRecordset.Close Any assistance will be appreciated. Mr. B -- HTH Mr B |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to open Access recordset via Query in Excel VBA??? | Excel Programming | |||
Open ODBC in Excel | Excel Programming | |||
Type recordset/recordset? | Excel Programming | |||
Microsoft][ODBC Excel Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN | Excel Programming | |||
open range (within workbook) as ado recordset - excel vba | Excel Programming |