Setting Import Path

[chris]

I have a database based on a number of tables, whose data is refreshed
fortnightly by importing several csv files.

A macro performs the import, but the problem is that the database is
used by people in different places, who keep their csv files in
different places. So, currently, the macro specifies the location of
the csv files, meaning that each user has to have a slightly different
version of the macro.

What I'd like to have is a form containing a text box where the user
could enter the path to their own csv folder - plus a 'browse' button
which would bring up the usual Windows Explorer dialogue box and allow
them to select the folder simply by clicking on it.

Is this possible?

Thanks in advance for any help!

Chris.

 

[ruralguy via AccessMonster.com]

Absolutely! Here's a link that will help:
http://www.mvps.org/access/api/api0002.htm

If it is more or less at a permanent folder each location then I would
suggest the value be in a table or a database property.
http://allenbrowne.com/ser-09.html

Each location would set it once and then forget it.

 

[chris]

Thanks very much for the help - but I'm very much a novice when it
comes to code, and I'm afraid you'll have to bear with me!

Thanks to the first of your links, I've now got a button which opens
the 'Browse for Folder' dialogue box - but what do I do next? How do
I get the selected path displayed/stored in the text box beside the
button and, more importantly, used by the import macro to find the csv
files it's looking for?

Chris.

 

[ruralguy via AccessMonster.com]

Will the csv files always be located in just one folder at each location?
What does your macro look like that imports the files?

Thanks very much for the help - but I'm very much a novice when it
comes to code, and I'm afraid you'll have to bear with me!

Thanks to the first of your links, I've now got a button which opens
the 'Browse for Folder' dialogue box - but what do I do next? How do
I get the selected path displayed/stored in the text box beside the
button and, more importantly, used by the import macro to find the csv
files it's looking for?

Chris.

Absolutely! Here's a link that will help:
http://www.mvps.org/access/api/api0002.htm

[quoted text clipped - 24 lines]

 

[chris]

Yes, the csv's will always be in one folder (they're downloaded in a
zip, so it's easiest for the users to extract them to a single
folder).

For each table that has to be updated, the import macro:

* Opens the table
* Selects all records
* Deletes all records
* Closes the table
* Transfers the records from the appropriate csv into the table it's
just emptied (this of course is where the path to the csv comes in)
* Repeats this sequence for all 6 tables

If converted to a module, the steps show as follows:

' Opens Localities Table
DoCmd.OpenTable "Localities", acViewNormal, acEdit
' Selects all records
DoCmd.RunCommand acCmdSelectAllRecords
' Deletes all records
DoCmd.RunCommand acCmdDelete
' Closes table
DoCmd.Close acTable, "Localities"
' ***Imports latest NPTG data from localities.csv in network
folder
DoCmd.TransferText acImportDelim, "", "Localities",
"\\ddhps03\rsp\Transport Direct Portal\Reference\Latest
NPTG\localities.csv", True, ""


Chris.

Will the csv files always be located in just one folder at each location?
What does your macro look like that imports the files?

Thanks very much for the help - but I'm very much a novice when it
comes to code, and I'm afraid you'll have to bear with me!

Thanks to the first of your links, I've now got a button which opens
the 'Browse for Folder' dialogue box - but what do I do next? How do
I get the selected path displayed/stored in the text box beside the
button and, more importantly, used by the import macro to find the csv
files it's looking for?

Chris.

Absolutely! Here's a link that will help:
http://www.mvps.org/access/api/api0002.htm

[quoted text clipped - 24 lines]

Chris.

 

[ruralguy via AccessMonster.com]

As I posted earlier, I would save the path selected in a table or database
property. Then fetch it to a variable when I needed it. I would probably
have a function in a standard module that returns the path and if it has not
been set yet, asks the user for it and saves it before returning. I’ll leave
that to you and just include the function name in the example. I’ll call it
MyPath(). In this case I will assume the path we saved is \\ddhps03\rsp\
Transport Direct Portal\Reference\LatestNPTG\

It would just change the TransferText line to:

DoCmd.TransferText acImportDelim, , "Localities", MyPath() & “localities.csv",
True

Post back if you need more assistance.

Yes, the csv's will always be in one folder (they're downloaded in a
zip, so it's easiest for the users to extract them to a single
folder).

For each table that has to be updated, the import macro:

* Opens the table
* Selects all records
* Deletes all records
* Closes the table
* Transfers the records from the appropriate csv into the table it's
just emptied (this of course is where the path to the csv comes in)
* Repeats this sequence for all 6 tables

If converted to a module, the steps show as follows:

' Opens Localities Table
DoCmd.OpenTable "Localities", acViewNormal, acEdit
' Selects all records
DoCmd.RunCommand acCmdSelectAllRecords
' Deletes all records
DoCmd.RunCommand acCmdDelete
' Closes table
DoCmd.Close acTable, "Localities"
' ***Imports latest NPTG data from localities.csv in network
folder
DoCmd.TransferText acImportDelim, "", "Localities",
"\\ddhps03\rsp\Transport Direct Portal\Reference\Latest
NPTG\localities.csv", True, ""

Chris.

Will the csv files always be located in just one folder at each location?
What does your macro look like that imports the files?

[quoted text clipped - 15 lines]

 

[chris]

Thanks yet again for your quick reply!

I *think* I'm beginning to understand, but I'm still not clear on how
the selected path is saved to a table (for instance).

Chris.

 

[ruralguy via AccessMonster.com]

Do you have a System Table that you can save site information in? Do you not
know how to use RecordSets or Update queries?

Thanks yet again for your quick reply!

I *think* I'm beginning to understand, but I'm still not clear on how
the selected path is saved to a table (for instance).

Chris.

As I posted earlier, I would save the path selected in a table or database
property. Then fetch it to a variable when I needed it. I would probably

[quoted text clipped - 10 lines]

Post back if you need more assistance.