most efficient way to check data

[token_remedie]

bit of a noob here, I've got an import function to import different spreadsheets to access, but the field names are slightly different all the time from sheet to sheet.
so what's the best way to get my data into a main table appending to the same columns that already exist in the table?

at the moment everything comes into an import table, if there's a way to check column names and run queries against all that - it would probably work. Also can I have a primary key that works across all tables for one record? so that i can change somethings in a record in one table but they wont change in the main table?

 

[Galaxiom]

Field names can be addressed from from the tabledef's Fields Collection in code by their numeric index, starting with Fields(0). This would allow you to read the names and build the query.

You might then require some way to match the fields with the name of the fields in the destination.

 

[token_remedie]

yah I'm trying to do something like:

Dim DB As DAO.Database
Dim tdf As DAO.TableDef, fld As DAO.Field

CheckField = False '--initialize to False
Set DB = CurrentDb
For Each tdf In DB.TableDefs
If tdf.Name = "importtable" Then
For Each fld In tdf.Fields
If fld.Name = "F10" Then fld.Name = "Location Code"
CheckField = True
MsgBox "field" & fld.Name ' testing output delete later
End If
Next
End If
Next


seems the biggest issue I've found so far are two columns that come from his spreadsheet are blank but I need them to not be.
is there a way to say something like:

if column name = f1 then column name = "number" and is a double
if column name = f2 then column name = "description" and is text
etc

that way is the columns are blank access renames them, i rename them again and build the table around this stupid range of spreadsheets?

 

[Galaxiom]

You can define the imported fields' properties in the Import Specification.

 

[token_remedie]

that's what I was hoping,

I've got this grabbing the sheet selection after the workbooks are dumped into a combobox:
Dim MyRange As String
MyRange = Me.Combo4 & "!A:ZZ"
MsgBox "" & strPathAndFile & MyRange


DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True, MyRange

am I on the right track?

 

[tablepad]

thanks for all the information you've shared.

 

[Galaxiom]

Maybe consider importing a CSV of the spreadsheet using TransferText Method

This allows a complete Import Specification to be defined.