Open recordset fails with ODBC connection

[Amduke]

Hello all,

I try to activate a recordset based on a DB2 table.
I setup a connection over ODBC and my check programm tells me that the
connection is OK (see sourcode hereafter)
Public Function GetState(intState As Integer) As String

Select Case intState
Case adStateClosed
GetState = "adStateClosed"
cnctd = False
Case adStateOpen
GetState = "adStateOpen"
cnctd = True
End Select

End Function

The problem I encounter is that I can't establish to open the recordset.
Can someone help me out with this. Is there a better way to populate the
recordset.
Here is the code I use:
Public Sub ChckCnn(Table As String)

On Error GoTo err_conn

odbcdsn = "DB2G"
UserID = "xxxxx"
Passwd = "xxxxxxxx"
portnumber = 4462

'Define connection string to DB2 with DB2connect
db2cnstr = "DSN=" & odbcdsn & ";HST=" & Host & ";PRT=" & _
CStr(portnumber) & ";UID=" & UserID & ";PWD=" & Passwd
Set db2con = New ADODB.Connection
db2con.Open db2cnstr
GetState (db2con.State)
With db2conn

Set rs_Chk = New ADODB.Recordset
'SqlChk = "SELECT " & Table & ".* FROM " & Table & ";"
SqlChk = Table & ";"
rs_Chk.Open rs_Chk.Source, db2con, adOpenForwardOnly, adLockReadOnly
Here the procedure goes wrong.

 

[Stefan Hoffmann]

hi Amduke,

Public Function GetState(intState As Integer) As String

Select Case intState
Case adStateClosed
GetState = "adStateClosed"
cnctd = False
Case adStateOpen
GetState = "adStateOpen"
cnctd = True
End Select

End Function

This function is really bad one, because it changes a control variable
outside of its scope. Further more, the later usage makes not really any
sense.

Public Sub ChckCnn(Table As String)

On Error GoTo err_conn

odbcdsn = "DB2G"
UserID = "xxxxx"
Passwd = "xxxxxxxx"
portnumber = 4462

'Define connection string to DB2 with DB2connect
db2cnstr = "DSN=" & odbcdsn & ";HST=" & Host & ";PRT=" & _
CStr(portnumber) & ";UID=" & UserID & ";PWD=" & Passwd
Set db2con = New ADODB.Connection
db2con.Open db2cnstr
GetState (db2con.State)
With db2conn

You don't need this With block.

Set rs_Chk = New ADODB.Recordset
'SqlChk = "SELECT " & Table & ".* FROM " & Table & ";"
SqlChk = Table & ";"
rs_Chk.Open rs_Chk.Source, db2con, adOpenForwardOnly, adLockReadOnly
Here the procedure goes wrong.

While it may be a permission problem on DB2, you should at least use SQL
statement with a qualified table name, e.g.

rsChk.Open "SELECT * FROM schema.table;", db2con, _
adOpenForwardOnly, adLockReadOnly

Besides that, what error message do you get?


mfG
--> stefan <--

 

[Amduke]

Hello Stefan,

thank you for the quick respond.
First I removed the connection control.
Second, I adjusted the open recordset script to:

rsChk.Open "SELECT * FROM schema.table;", db2con, _
adOpenForwardOnly, adLockReadOnly

The error message is: 424 Object required.

 

[Stefan Hoffmann]

Second, I adjusted the open recordset script to:

rsChk.Open "SELECT * FROM schema.table;", db2con, _
adOpenForwardOnly, adLockReadOnly

The error message is: 424 Object required.

Maybe a typo.

First of all, at the top of all your code modules there must be these
two lines:

Option Compare Database
Option Explicit

After placing them there, run Debug\Compile. This may stop at db2con.

Rewrite your code using explicit declarations:

Public Sub ChckCnn(Table As String)

On Local Error GoTo LocalError

Const odbcdsn As String = "DB2G"
Const UserID As String = "xxxxx"
Const Passwd As String = "xxxxxxxx"
Const portnumber As String = 4462

Dim db2con As ADODB.Connection
Dim rs_Chk AS ADODB.Recordset

Dim db2cnstr As String
Dim SQL As String

'Define connection string to DB2 with DB2connect
db2cnstr = "DSN=" & odbcdsn & _
";HST=" & Host & _
";PRT=" & CStr(portnumber) & _
";UID=" & UserID & _
";PWD=" & Passwd

Set db2con = New ADODB.Connection
db2con.Open db2cnstr

Set rs_Chk = New ADODB.Recordset
SQL = = "SELECT " & Table & ".* FROM " & Table & ";"
rs_Chk.Open SQL, db2con, adOpenForwardOnly, adLockReadOnly
'further code...

Exit Sub

LocalError:
'your error handler.
End Sub

mfG
--> stefan <--

 

[Amduke]

Stefan,

the 2 top lines in my code are already in it.
I adjusted the code with the SQL statement and this is the error message
that pops up:
-2147217865:[IBM][CLI Driver][DB2]SQL0204N "MyID.MyDb2File" is an undefined
name. SQLSTATE:42704

As extra information, I have this problem for al long time now. To avoid
problems in other programs, I just inserted some code to ignore the error
messages, and all these programs can use the Db2 tables that are in the
database as linked tables over ODBC.
These Db2 tables are accessed by the standard Access query's; and the
programs work just fine.
The reason I was looking for this kind of connection, is to have more
control over the program as the system does not allow to access the table due
to an update.