01-12-2004, 10:17 AM
I am importing like 9 tables/dbf files on a cd into access. I set up a table with the file names and paths so I can write a do while not eof# loop until all files are imported so if file names change it wont be difficult for someone to change. I cant remember how to set the data from the record set to the variables.

Any help?

01-12-2004, 10:22 AM

You mean something like?:

Dim a as Double
a= rs.Fields(0).Value


01-12-2004, 10:26 AM
yes that is exactly what I wanted. I.E., field = Actual Field Name? the (0) = Field order ect..?

01-12-2004, 10:33 AM
no really

Fields()- is a collection consisting of Field objects. You can refer to a
specific field either by name or by index

so rs.Fields("Field1").Value is basically similar to rs.Fields(0).Value
(provided that "Field1" is the first field in the recordset)

01-12-2004, 12:21 PM
This is in an access module

Public Function ImportData()
Dim dbs as database
Dim Rst as recordset
Dim mysql as string

Set dbs = CurrentDb
mySql = "Select * from Tbl_GetData"

Set rst = dbs.execute(mySql) 'This is where I am having and issue now

Do Until rst.EOF

dbtype = rst.Fields(4).Value
filename = rst.Fields(3).Value
PathName = rst.Fields(2).Value
TableName = rst.Fields(1).Value

DoCmd.DeleteObject acTable, TableName
DoCmd.TransferDatabase acImport, dbtype, PathName, acDefault, filename, TableName, 0, True



End Function


01-12-2004, 03:33 PM
so what is your current issue?

If you want to loop an entire recordset until EOF, you also probably want to use the method instead of trying to use the execute method of the ADO connection. The .execute method will provide a forward only cursor...but that really shouldn't effect this.

But again, I am not sure where the problem is? Is the execute statement not opening your recordset?

01-12-2004, 03:42 PM
I have corrected the issue, one stupid little typo, but that is usually what causes all the problems. I was DOA.

But if you are interested in a helping me with another brain freeze,

I have to run updates against a table.

I insert a column, then based on the values of one column I update the other.


If Value of ist column is 15 characters long then
Trim off the first 2 charaters
Then update 2nd column

update second column

End if

I have no clue how to write this.

01-12-2004, 05:50 PM
>If Value of ist column is 15 characters long then
Trim off the first 2 charaters
Then update 2nd column

update second column

End if

Hoping that I'm doing what you would like to:

If Len(rs.Fields(0).Value) = 15 Then
rs.Fields(1).Value = Left(rs.Fields(0).Value, 2)
rs.Fields(1).Value = rs.Fields(0).Value
End If


01-13-2004, 01:31 PM
That is exactly what I was looking for. I could not remember LEN
However, I am getting an error at

If Len(rst.Fields(0).Value) = 15 Then
rst.Fields(1).Value = Left(rst.Fields(0).Value, 2)

'errorTime Runerror 3020; Update or cancelupdatewith outaddnew or edit.

rst.Fields(1).Value = rs.Fields(0).Value
End If


01-13-2004, 04:35 PM
Well, difficult to say but it seems that after opening the recordset you are trying to do something with this (update, addnew?)
And then apply the code.
Just for the info how are you opening your recodset and what are you trying to do with this after?

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum