user defined import maps

  • Thread starter Thread starter David M via AccessMonster.com
  • Start date Start date
D

David M via AccessMonster.com

I'm looking for some guidance in creating a form to allow users to define
their own import maps.

The users will be importing data from different text files, and I need them
to be able to create field maps as needed without requiring additional
porgramming.

I can handle the form creation, although I would appreciate any suggestions
about that too. What I really could use some help with is the coding behind
the form that creates the import SQL.

Thank you,
David
 
Assuming (you don't say) that these are delimited files with field names
in the first row, I'd think in terms of a form with a series of labels
carrying the names of the fields in the Access table, and beneath each
label combobox containing the names of the fields in the text file.

(If you access the text files as linked tables, you can just set the
combos' RowSourceType to FieldList.)

For example, an Access table with fields FirstName, LastName,
DateOfBirth. You'd have a "FirstName" label above cboFirstName, and so
on. Each combo would have the list of fields from the text file, e.g.
"FName", "LName" "DOB".

Then you'd start with a constant to contain the bones of an append query

Const SQL1 = "INSERT INTO MyTable (FirstName, LastName, " _
& " DateOfBirth) SELECT "

and get going with something like
Dim strSQL AS String
Dim dbD As DAO.Database

strSQL = "SQL1 & """ & Me.cboFirstName.Value & """, """ _
& Me.cboLastName.Value & """," _
& CDate(Me.cboDateOfBirth.Value) _
& " FROM " & blah blah & ";"

Set dbD = CurrentDB()
With dbD
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "Something went wrong"
Else
MsgBox Cstr(.RecordsAffected) & " records appended."
End If
End With
 
John,

That's very helpful. I will look for info on linking tables by code and go
from there.

Thanks,
David
 
Back
Top