Unanswered: Specifying & Accessing an array in MS Access
I have an Excel spreadsheet with 11 columns. To do what I want to do, this file will be converted to a flatfile. I want to read each record in the file and lookup on the database, the name in the 11th column to see if the name is already loaded into the dtabase.
Does anyone know how I can do this and, can you give me the code to define the array and loop through the array to reach column 11?
Access does not use arrays to read separate columns in a table. You can use the name of the field that contains the data from the 11th column. I assume you want to process this flat file for any dups, then add the none dups to the master table in the database?
ok What I would do is can you LINK the Text file to the Database
if so know you have it in one systems
Right know the Code
open a new module.
Dim db As Database ' create a database object
Dim RS As Recordset ' create a recordset object
Dim rsnew As Recordset
Set db = CurrentDb
Set RS = db.OpenRecordset("the linktable name")
Set rsnew = db.OpenRecordset("table name of new data going in to")
Do Until RS.EOF
checkfield = RS.Fields("the name of the 11th column") 'this will put the value in to checkfield
If countit = 0 Then
rsnew.feild("feildname") = RS.Fields("feildname")
Set RS = Nothing
Set rsnew = Nothing
code off top of head needs testing that should point you down the right track
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.