Field name substitution in ACC2000?

[Guest]

Hello,

I'm a very occasional VB programmer who remembers being able to do such
things as macro substitution for field names in FoxPro. For example, if
fieldname = "address", then using &fieldname would refer to a field named
address. Is there similar functionality in ACC2000? The best I can come up
with is manipulating SQL statement strings.

Many thanks.

George

 

[Douglas J. Steele]

Nothing similar in VBA.

What's the exact problem you're trying to solve?

 

[Guest]

What's the exact problem you're trying to solve?

--

Well, since you ask...

I'm putting together an application for a local non-profit that takes a csv
e-mail attachment from a formmail script and uses it to populate more than
one table. For example, a non-profit organization could submit a form with
both contact info and volunteer opportunity info.

On import the csv file is dumped into a table with fields matching the csv
file. I would then like to look up this table's field in a translation table
to determine the table and field where the data should properly go.

Of course, if a more straightforward method exists, I would be a happy camper.

Thanks.

George

 

[Douglas J. Steele]

I'm putting together an application for a local non-profit that takes a
csv
e-mail attachment from a formmail script and uses it to populate more than
one table. For example, a non-profit organization could submit a form
with
both contact info and volunteer opportunity info.

On import the csv file is dumped into a table with fields matching the csv
file. I would then like to look up this table's field in a translation
table
to determine the table and field where the data should properly go.

Of course, if a more straightforward method exists, I would be a happy
camper.

I think you're going to have to give an example: that's still too abstract
for me.

 

[John Nurick]

PMFJI,

If you just want to get the names of the fields in the new table, you
can open a recordset and look through its Fields collection:

Sub ListFieldsInTable(TableName As String)
Dim rsR As dao.Recordset
Dim fldF As dao.Field
Dim j As Long

'Open a recordset with no actual records
Set rsR = CurrentDb.OpenRecordset("SELECT * FROM " & TableName _
& " WHERE FALSE;", dbOpenSnapshot)

With rsR.Fields
For j = 0 To .Count - 1
Debug.Print .Item(j).Name
Next
End With

rsR.Close
End Sub

If I understand the situation right you're thinking in terms of a
translation table that maps the field names you expect to find in the
incoming csv files onto the names you have used in your "permanent"
table, e.g.
CSVField, PermanentField, TableName
FirstName, FirstName, Contacts
FName, FirstName, Contacts
First Name, FirstName, Contacts
...

If so, you can call DLookup() in the For..Next loop, and use the values
it returns to build the SQL statement for an append query that maps the
field names for you, along these lines:

INSERT INTO Contacts (FirstName, MiddleName, LastName ...)
SELECT FName, MI, LName ... FROM CSVTable;

 

[Guest]

If so, you can call DLookup() in the For..Next loop, and use the values

it returns to build the SQL statement for an append query that maps the
field names for you, along these lines:

INSERT INTO Contacts (FirstName, MiddleName, LastName ...)
SELECT FName, MI, LName ... FROM CSVTable;

Doug & John,

Thanks for all your replies.

Doug, here's an example from a translation table:
fformfield ftblname ffldname
DayReqd topp fdayreqd
email tnpo fEmail
(hope formatting is not lost)
where fformfield is the field name as it appears in the csv file; ftblname
is the destination table; ffldname is the field in the destination table.

John,

Thanks for the insight on using dlookup() in the loop.

I was hoping for some elegant way to build field names, maybe using ordinals.

George