Importing data using Access 2007
I have created a text file specification using the import wizard in Access 2007. I want to automate this process and have the user run a macro which will make a call to a vb fuction. In my code see below I use the name of the import specification as one of the function arguements. When the macro runs I receive an error stating the text file specification does not exist. When I go into access I can see the specification I saved.
Function importData()
DoCmd.TransferText acImportDelim, "TXMAST1", "TXMAST", "TXMAST.txt"
End Function
Aaronroach
Try giving a complete File path in the last element i.e. "C:\TXMAST.txt"
ASKER
same error.
Juneaucounty,
Are you sure you have an "import specification" in the current DB named:
TXMAST1
Make sure it is spelled correctly.
You stated that:
"When I go into access I can see the specification I saved."
Where, specifically, are you going in Access that you can see the import Spec?
JeffCoachman
Are you sure you have an "import specification" in the current DB named:
TXMAST1
Make sure it is spelled correctly.
You stated that:
"When I go into access I can see the specification I saved."
Where, specifically, are you going in Access that you can see the import Spec?
JeffCoachman
ASKER
In access 2007 go to the external data tab, then click the "saved imports" button. See screen shot.
save-import.jpg
save-import.jpg
OK,
Just making sure.
(BTW, it still wont hurt to add the full path in the code as Aaronroach suggests)
Try changing the output files extension to ".csv"
DoCmd.TransferText acImportDelim, "TXMAST1", "TXMAST", "y:\TXMAST.csv"
Also, ... is this an Access 2003 file that has been converted to Access 2007, or simply an Access 2003 file you are running under Access 2007?
If so, try recreating the Specification from scratch in Access 2007.
JeffCoachman
Just making sure.
(BTW, it still wont hurt to add the full path in the code as Aaronroach suggests)
Try changing the output files extension to ".csv"
DoCmd.TransferText acImportDelim, "TXMAST1", "TXMAST", "y:\TXMAST.csv"
Also, ... is this an Access 2003 file that has been converted to Access 2007, or simply an Access 2003 file you are running under Access 2007?
If so, try recreating the Specification from scratch in Access 2007.
JeffCoachman
ASKER
You suggested:
Try changing the output files extension to ".csv"
DoCmd.TransferText acImportDelim, "TXMAST1", "TXMAST", "y:\TXMAST.csv"
y:\TXMAST.TXT is the input file not the output file.
I know the path/file name is correct because if I change this I receive a different error. I am using access 2007 and my database is in 2002-2003 file format. I tried what you suggested and created a new 2007 database and import spec. Same error unfortunately. Thanks for your help btw.
Try changing the output files extension to ".csv"
DoCmd.TransferText acImportDelim, "TXMAST1", "TXMAST", "y:\TXMAST.csv"
y:\TXMAST.TXT is the input file not the output file.
I know the path/file name is correct because if I change this I receive a different error. I am using access 2007 and my database is in 2002-2003 file format. I tried what you suggested and created a new 2007 database and import spec. Same error unfortunately. Thanks for your help btw.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found the issue. When you create an import specification using the wizard there is an advanced button, click it and you get a dialog with a save as button. This will be the real name of the specification that I needed to reference in the code, which is different than the name you use when finishing the wizard when you want to save the export. Kind of confusing since there is no way to see the saved specifications except to run through the wizard again. I appreciate your help boaq2000. See attached screen shot for details.
spec-problem.jpg
spec-problem.jpg
ASKER
thanks for you help!!!!
Stange but true I guess.
;-)
;-)