append dbf file to access table

[DNRtech]

I've found several code solutions for this task but have yet to find one that works for me or is elementary enough for me to preform successfully.

I'm am exporting dbf files from a program called EcoWin and I need to append those files to tables in Access, preferably in Access 2007.

I have set up an event button on the form and have entered the following code into the VisualBasic utility accessed via the ellipses in the "On Click" dialog found in the Property Sheet for my command button:

Option Compare Database

Private Sub Command18_Click()
CurrentDb.Execute "INSERT INTO [CAMPBELL] SELECT * FROM " & _
"[dBase IV;DATABASE=C:\Documents and Settings\jtaylor\Desktop\;].[07_29_~1.DBF]\;"
End Sub


I found this code on another thread on this site.

I have the set the "On Click" dialog to "[Event Process]". When I click the button, nothing happens, not even an error.

Am I missing a step? Is this code appropriate for what I want to do?

Thanks for the help!

 

[DNRtech]

It appears that I Access was "protecting" me from the error message. I now have a syntax error in the second line. I will try to sort this out.

 

[vbajock]

Generally this chore is done by using the Transferdatabase method:

http://www.blueclaw-db.com/transferdatabase-docmd.htm

 

[DNRtech]

Maybe I missed it but Transferdatabase process seems to do what the wizard already offers in Access 2007, where the DBF is added as a new table or a link. What I'm trying to do is append the data from a dbf to an access table that already exists with the same schema, just like you can do via the wizard with a page from an Excel workbook.

 

[DNRtech]

Ha! I just got it to work!

Here's the VB code:

Option Compare Database

Private Sub Command18_Click()
CurrentDb.Execute "INSERT INTO [DesinationTable] SELECT * FROM " & _
"[dBase IV;DATABASE=C:\DirectoryToDBF;].[DBFtoAppend];"
End Sub

 

[vbajock]

Transferdatabase imports the file into the database as a new table

http://msdn.microsoft.com/en-us/library/bb214131(v=office.12).aspx

but it's great you figured out how to do it thru SQL.