access/vba

swoozie
01-12-2004, 09: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?

Shurik12
01-12-2004, 09:22 AM
Hi,

You mean something like?:

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

Regards,
Shurik.

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

Shurik12
01-12-2004, 09: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)

swoozie
01-12-2004, 11:21 AM
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

rst.MoveNext

Loop

End Function

Thanks

MKoslof
01-12-2004, 02: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 recordset.open 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?

swoozie
01-12-2004, 02: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.

I.e.,

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

else
update second column

End if

I have no clue how to write this.

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

else
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)
Else
rs.Fields(1).Value = rs.Fields(0).Value
End If
rs.Update


I

swoozie
01-13-2004, 12: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.

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


Thanks

Shurik12
01-13-2004, 03: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