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

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






"Spammastergrand" <(E-Mail Removed)> wrote in message
news:cM9Eb.2360$(E-Mail Removed)...
> 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
>
>
>

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: <u4BQE$(E-Mail Removed)>

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 Removed)