Pirke
01-20-2006, 05:27 AM
Hi all,
a few days back I asked about how to connect in VBa with ODBC to an sql-database. Ken and Rembo helped out with setting up an ADODB.connection.
With the first table in the database this works without problems. But when I used the same style of code to connect to another table in the same database, I only get the fieldname of the field, and not the value of the field. I can get some other information back (like recordcount, status, ActualSize etc) but when i 'ask' for the value, all I get is the name.
Does anybody have any idea what I do wrong?
the code:
(all variables are declared (public) in a separate module, and the strings for the connection also)
Sub GetNdaBasic()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Set conn = New ADODB.Connection
strConnection = strDriver & strServer & strDatabase & strUID & strPWD & strOption
conn.ConnectionString = strConnection
sql = "SELECT 'id', 'statuslevel' FROM `nda_basic` WHERE (`id` = '10');"
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, conn
str1 = rs.Fields(0).Name
str2 = rs.Fields(1).Name
str3 = rs.Fields(0).Value
str4 = rs.Fields(1).Value
MsgBox str1
MsgBox str2
MsgBox str3
MsgBox str4
rs.Close
conn.Close
End Sub
str1 and str3 are the same, and str2 and str4 are also the same
Thanks for any input on this one!
Peter
a few days back I asked about how to connect in VBa with ODBC to an sql-database. Ken and Rembo helped out with setting up an ADODB.connection.
With the first table in the database this works without problems. But when I used the same style of code to connect to another table in the same database, I only get the fieldname of the field, and not the value of the field. I can get some other information back (like recordcount, status, ActualSize etc) but when i 'ask' for the value, all I get is the name.
Does anybody have any idea what I do wrong?
the code:
(all variables are declared (public) in a separate module, and the strings for the connection also)
Sub GetNdaBasic()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Set conn = New ADODB.Connection
strConnection = strDriver & strServer & strDatabase & strUID & strPWD & strOption
conn.ConnectionString = strConnection
sql = "SELECT 'id', 'statuslevel' FROM `nda_basic` WHERE (`id` = '10');"
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, conn
str1 = rs.Fields(0).Name
str2 = rs.Fields(1).Name
str3 = rs.Fields(0).Value
str4 = rs.Fields(1).Value
MsgBox str1
MsgBox str2
MsgBox str3
MsgBox str4
rs.Close
conn.Close
End Sub
str1 and str3 are the same, and str2 and str4 are also the same
Thanks for any input on this one!
Peter