I'm having troubles connecting to a DB2 table (or setting up the ADODB connection) It works fine when I'm hitting an access table, but for some reason will not work with the DB2 table. The table, of course is linked via ODBC and I can connect to it when running an Access query. I'm trying to populate a single text box on a form with a count derived from the following query. I'm getting the error message: "Run-time error "-2147217565(80040e37) [IBM][CLI Driver][DB2] SQL0204N "DIW07.PROD_VLTC900A_CLM" is an undefined name. SQLSTATE=42704.
The only thing i can think of is that it's taking my username "DIW07" and using it as the database name? I'm not sure why this would be an issue as the table is linked... it's a real pain... Any help would be HUGELY appreciated!
Here is the code I'm using:
Private Sub CalcKim_Click()
Dim rsActivity As ADODB.Recordset
Dim cnUDB As ADODB.Connection
Set cnUDB = New ADODB.Connection
.Provider = "MSDASQL"
.ConnectionString = "Data Source=db2psn"
.Properties("Prompt") = adPromptCompleteRequired
sSQL = ""
sSQL = sSQL & "SELECT Count(I_SYS_CLM)AS thirtydayKim "
sSQL = sSQL & "FROM PROD_VLTC900A_CLM p INNER JOIN Teams t ON p.Custodian = t.CustodianID "
sSQL = sSQL & "WHERE p.PendingStatus = 'PC' And t.Manager = 'Kim' "
'And DateDiff('d',[DatePending],Date()) <=30"
Set rsActivity = New ADODB.Recordset
.CursorType = adOpenForwardOnly
.CursorLocation = adUseServer
.LockType = adLockReadOnly
.Open sSQL, cnUDB
Me.k2 = rsActivity!thirtydayKim