UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Import Data From .dbf File, Office 2007    
 
   
cwac60
post Dec 16 2010, 12:23 AM
Post#1



Posts: 820
Joined: 1-April 09
From: Queensland Australia


Please help as I need to use VB to import data from a dbf file.
While it is easily performed through the front end (External Data) either as a link or import, I have not been able to find an example showing:
• How to prompt for a file name – including ability to browse to a folder to find the .dfb file
• Create a link to the .dbf file
• Insert some of the rows into an existing MS Access table.
HAs mentioned above I have done these steps manually but need to VB approach.
I have already created a post re the 8.3 file name limitation.
Regards
cwac60
Go to the top of the page
 
HiTechCoach
post Dec 16 2010, 01:33 AM
Post#2


UtterAccess VIP
Posts: 19,009
Joined: 29-September 03
From: Oklahoma City, Oklahoma


Here is some VBA code that should help:
o get the file:
Call the standard Windows File Open/Save dialog box
To create the linked table use this VBA method: TransferDatabase
You can use an append query to copy records form the newly linked table ( .dbf) to your Access table.
You will probable want to delete the linked table object when done.
Go to the top of the page
 
cwac60
post Dec 16 2010, 07:56 AM
Post#3



Posts: 820
Joined: 1-April 09
From: Queensland Australia


Thank you HiTechCoach for your advice. Initially I am trying to load the data from the dbf file but continually getting errors.
The MS Access 2007 database is Test.accdb located in folder "D:\MS Access Job\101216\". I have verified the path is correct
by using Dir command to display it.
The dBSAE file is "D:\Cabsch.dbf"
The MS Access table is "cab" which I created by using a Db link to select from and insert into the data into it to ensure the same
table structure as the dBASE file.
I have a button to run the Sub xfer
THere are 2 examples of invalid syntax error messages
Option Compare Database
Private Sub xfer_Click()
DoCmd.TransferDatabase acImport, "dBase III", "D:\MS Access Job\101216\Test.accdb", acTable, "D:\Cabsch.dbf", "cab"
'Run-time error 3044 - Invalid Path
End Sub
Option Compare Database
Private Sub xfer_Click()
DoCmd.TransferDatabase acImport, "dBase III", , acTable, "D:\Cabsch.dbf", "cab"
'Run-time error 3001 - Invalid Argument. Here I left out the 'Optional' path and left a blank parameter i.e. ,, is that still acceptable?
End Sub
To try an alternative example i.e. Export I get a different error
Option Compare Database
Private Sub xfer_Click()
DoCmd.TransferDatabase acExport, "dBase III", "cab", "d:\exporthist.dbf"
'Run-time error ‘13’: Type mismatch
End Sub
Your advice would be much appreciated.
Regards
cwac60
Go to the top of the page
 
RJS
post Dec 16 2010, 01:31 PM
Post#4



Posts: 667
Joined: 7-November 01
From: Arizona


This works for me.
put it together many years ago and it has not ever given me a problem.
DoCmd.TransferDatabase acImport, "dBase IV", "G:\DAT", acTable, "AR-Import.dbf", "AR-Import", False
Rob
Go to the top of the page
 
cwac60
post Dec 16 2010, 03:16 PM
Post#5



Posts: 820
Joined: 1-April 09
From: Queensland Australia


Thank you Rob
Thank you so much - it is a great relief to run the command below without a syntax error.
oCmd.TransferDatabase acImport, "dBase IV", "D:\", acTable, "Cabsch.dbf", "cab", False
Hwever instead of populating the cab table, it creates a new table cab1 and populates it leaving cab empty.
Is there any way of getting it to populate the original destination table ("cab")?
I have based the action using the 'On Click' event and tend to double click > cab1 and cab2 etc, is there any convention re
use of 'On Click' or 'On Dbl Click' or if using 'On Click' to ensure it only performs once?
Regards
cwac60
Go to the top of the page
 
RJS
post Dec 17 2010, 11:30 AM
Post#6



Posts: 667
Joined: 7-November 01
From: Arizona


I'm not sure, but I do not think there is an option to append to a table with that command. It will always create a new table.
ptions:
1. Build a query to update your table from what you just imported (you will have to delete the "temp" table each time so you are always working with the same imported table name)
2. If you are always importing the same file name from the same location, why not just link to that file and run an update query from the linked file to the table you want to update.
Hope this helps,
Rob
Go to the top of the page
 
cwac60
post Dec 20 2010, 09:31 PM
Post#7



Posts: 820
Joined: 1-April 09
From: Queensland Australia


Hi Rob,
followed your advice and now all is working file - thanks to you.
1. I build command to delete the table, using a function run prior to test if it existed
2. I have successfully created the link alternative - still deciding which way to go.
I have set up a form to allow input (a) file name (dbf) and (b) path to make it more general and extract these values into strings
e.g. Folder = Forms!formname!Text7
This makes it easier to manipulate both parts rather than G:\Access\MyFolder\dBasetbl.dbf as I would then need to parse the single string to separate out
(a) G:\Access\MyFolder\
(b) dBasetbl.dbf
If anyone has a more elegant way of handling the input parameters for ultinate use in the method (TransferDatabase), I woul be pleased to know.
Thanks again
Regards
cwac60
Go to the top of the page
 
RJS
post Dec 22 2010, 10:22 AM
Post#8



Posts: 667
Joined: 7-November 01
From: Arizona


Glad to hear it is working.
You are basically doing the same thing as a program I build many years ago. It imported several DBFs automatically everynight for years without any issues. We eventually moved the process to SQL server and DTS / SSIS took over that part of the process.
Rob
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 03:11 AM