Need to name import table

[Guest]

Hi there everyone! Using A02 on XP. Not a programmer but am discovering the
joys of VB and have worked with macros. I have a project and want to be sure
I'm going in the best direction.

We have an admin system that generates an annual file to go to each client
for census info updates. We want to 'remove' the social security numbers
before 'sending' an Excel file to each client and then 'reinsert' the ssn's
when the file returns from the client before uploading back into the admin
system.

I'm creating a DB that will import the initial system data
file(S:\RPS\Census\GP0001.txt) into a table that will be named GP0001. If
the file name is GP0002 or GP0003, the table should be named GP0002 or
GP0003. I have created a form that has all the contracts listed via a query.
I'd like to have the user select the correct contract number and when they
click the command button, the DB will go find the file named with that same
contract number and import the data into a table that will also be named with
that contract number.

I started with a macro but wonder if I can put an expression in the Table
Name and/or File Name items when using TransferText so I can use the file
name in the Table Name. Or should I put this in VB on the button click
event? Wasn't sure.

Thanks to any and all that try to help. Thanks in advance for any help or
advice!

Bonnie

 

[Ken Snell \(MVP\)]

You can use an expression similar to this as the FileName argument of a
macro:

="C:\MyFolder" & Forms!FormName!ControlWithFileChoice & ".txt"

VBA can do similar things in the DoCmd.TransferText, except you don't need
the = sign there.

 

[Douglas J. Steele]

Just to tag along on Ken's correct answer, I thought I'd point out that if
the text in ControlWithFileChoice contains blanks, you'll likely need to
enclose the whole file name in quotes:

="""C:\MyFolder" & Forms!FormName!ControlWithFileChoice & ".txt"""

 

[Guest]

Thanks Ken. You answered my other question posted also about "Need autonum
in table". I think your idea of using just one table is best with the
autonum field already in it and just need to know how to 'pad' the file name
into a field in my table upon importing. The contract number will be on my
form, selected in a list box as well as being the actual file name. Is it
better to pad the field from the file name or from the field on my form?
Hoping to hear back from you on that. Would like to know both if possible.

Thanks for the info you have provided already, it will be helpful. I'll
print it out and keep it as I am learning some cool code lately.

Thanks also for being willing to help in the newsgroups. It makes a huge
difference to folks like me. Happy holidays and may life be good to you year
round!

 

[Guest]

Thanks Douglas!!! Those darn quotes....they get me every time...

Happy holidays and thank you for also being part of the newsgroups. It does
mean alot to me throughout the year.