Use textbox value in Form to Change file location in saved import specs (MS Access)

Hi Guys & Gals,

I'm working on an Access project (MS access 2010) & I have an unbound form called "Import Into Account" that I have a file picker/browser to choose the file to import and I want to have VBA update the saved import spec to the file chosen in the file picker/browser on the form, then execute an update query and close the form. I think I have most of the moving pieces good to go, but I don't know how to the file location in the saved import specs to change. Here's the info:

Form Name: Import Into Account
File Path for import csv file: txtImportFileLocation (unbound text box on form)
"filter" for which saved import specs to use: cbImportAccount (unbound dropdown with a list of account names)
button to make it all go: btnImportAccountRunQuery

The saved import specs are named "Chase" and "GTK" & I want it to work like:

Private Sub btnImportAccountRunQuery_Click()
If cbImportAccount = 2 Or cbImportAccount = 3 Then 
ImportSpecifications = "Chase"  
ImportFilelocation = txtImportFileLocation 
DoCmd.OpenQuery "Chase Import Query", acViewNormal, acEdit
Dim UpdateImportedSQL As String
UpdateImportedSQL = "UPDATE [Chase Import] " & _
        "SET Imported = TRUE; "
DoCmd.RunSQL UpdateImportedSQL
DoCmd.Close acForm, "Import Into Account"

Else If If cbImportAccount = 12  Then 
ImportSpecifications = "GTK"  
ImportFilelocation = txtImportFileLocation 
DoCmd.OpenQuery "GTK Import Query", acViewNormal, acEdit
Dim UpdateImportedSQL As String
UpdateImportedSQL = "UPDATE [GTK Import] " & _
        "SET Imported = TRUE; "
DoCmd.RunSQL UpdateImportedSQL
DoCmd.Close acForm, "Import Into Account"
End If
End Sub

Open in new window



Any help would be most appreciated!

Kate
LVL 1
JokoLennonoAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure the Import Spec contains the actual file location of the importing file. If you want to import a file and have the user choose it, and you've stored that value in "txtImportLocation", then just do this:

DoCmd.TransferText acImportDelim, "YourSpecName", "YourTableName", txImportLocation

See here for more on TransferText: https://msdn.microsoft.com/en-us/library/office/ff835958.aspx

After importing, you can then run your queries and such.
0
 
JokoLennonoAuthor Commented:
DoCmd.TransferText acImportDelim, "YourSpecName", "YourTableName", txtImportLocation

worked perfectly, thank you for your help!
0
 
Helen FeddemaCommented:
Try my New Style Main Menu (here is a link to the article and sample database):

http://www.helenfeddema.com/Files/accarch212.zip

It allows you to select a path (or several paths) once, to be used throughout the database.  Here is a screen shot of the main menu:

New Style Main Menu
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.