DAO error getting recordset

[Guest]

I'm having trouble getting a recordset object from an external Access DB
using a form.
The tb object works giving me recordcounts and whatnot from the table in the
external database
But the recordset won't work. I want to pull values in fields: Rval =
rs.Fields(i).Value
Am I missing something?


Dim Qry1 As String, TabStr1 As String
Dim FileToOpen As String
Dim DB As Database
Dim tb As TableDef
Dim rs As Recordset

FileToOpen = Me.DBox.Value 'External DB Filename
TabStr1 = "[" & Me.TableBox.Value & "]" 'Table name
Qry1 = "SELECT * FROM " & TabStr1
Set DB = DBEngine.Workspaces(0).OpenDatabase(FileToOpen)


Set rs = DB.OpenRecordset(Qry1, dbOpenSnapshot)

Set tb = DB.TableDefs(Me.TableBox.Value)

MsgBox "Importing " & tb.RecordCount & " records from " & _
tb.Fields.Count & " fields with the statement" & vbCr & Qry1

Dim Rval
dim i as integer

for i = 0 to 10

Rval = rs.Fields(i).Value

msgbox Rval
next i

 

[Michel Walsh]

Hi,


Since RVal is a scalar, it can only hold one single value. You would get the
same result if, instead of

Dim Rval
dim i as integer

for i = 0 to 10

Rval = rs.Fields(i).Value

msgbox Rval
next i


you would say:


Dim Rval
Rval=rs.Fields(10).Value


In that case, Rval would get the value in the 11 th field, first record.
That's all. Where do you want the other fields? the other records?


DIm i As long
Do Until rs.EOF
For i = 0 to 10
Debug.Print rs.Fields(i);
Next i
Debug.Print vbNullString
rs.MoveNext ' change record
Loop


would print, in the immediate debug window, the first 11 fields, all
records.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I solved that problem by using isnull(raval) then
rval = ""

I still have a problem with dates though, but I'm not sure anyone knows.
Sending to SQL server you don't surround the dates with # symbols.
Unfortunately when I remove them I get something like 01/01/1900 on all date
fields on export to SQL. I wonder If I have to use a date function or convert
to text with ' ' in SQL.

Subject: Re: DAO error getting recordset
From: "Michel Walsh" vanderghast@VirusAreFunnierThanSpam
Date: 12/18/2003 7:11 AM Eastern Standard Time
Message-id: <[email protected]>

Hi,


Since RVal is a scalar, it can only hold one single value. You would get the
same result if, instead of

Dim Rval
dim i as integer

for i = 0 to 10

Rval = rs.Fields(i).Value

msgbox Rval
next i


you would say:


Dim Rval
Rval=rs.Fields(10).Value


In that case, Rval would get the value in the 11 th field, first record.
That's all. Where do you want the other fields? the other records?


DIm i As long
Do Until rs.EOF
For i = 0 to 10
Debug.Print rs.Fields(i);
Next i
Debug.Print vbNullString
rs.MoveNext ' change record
Loop


would print, in the immediate debug window, the first 11 fields, all
records.



Hoping it may help,
Vanderghast, Access MVP

(e-mail address removed)