Importing Fox Pro Database to Access....

[Robert88]

Hi All,

If anyone can help I would appreciate it.

I have scanned the internet and this site to not much avail......

But however I did stumble across this code in another site which contains the following code;

Private Sub CmdReadDBF_Click()

 DoCmd.TransferDatabase acImport, "ODBC", _
  "ODBC;DSN=CDLabel;SourceD*B=D:\;SourceType=DBF", _
  acTable, "test.dbf", "tblTest_dbf"
  
  MsgBox "File transfer to tblTest_dbf should be complete? (Cross our fingers)."

End Sub

Unfortunately I cannot seem to get it to work, can anyone see what I might be doing wrong? Just trying to get the data from test.dbf to a table tblTest_dbf.

I am trying to read a file test.dbf from a cd that is drive D:.

Any help would be appreciated.

Robert88

 

[Robert88]

OK so I can see I am not going to get too much help here.

Seems the internet does not offer much either....... Hmmmmm....

Any help would be appreciated.........

Robert88

 

[RV]

OK so I can see I am not going to get too much help here

You could help yourself by searching the forum, for instance on..... dbf

RV

 

[Robert88]

Hi RV,

Good call, but unfortunately what I have worked out is searching dbf is very general and have already tried this.

I seem to be beating my head against the wall when it comes to Fox Pro support.

There is something about ODBC which I am not quiet clear on.

In order to import a dbase III, IV or V dbf it is pretty straight forward but when it comes to Fox Pro it seems not so straight forward.

I have now tried;

Private Sub CmdReadDBF_Click()

 DoCmd.TransferDatabase acImport, "Visual FoxPro Tables", "D:\", acTable, "test", "tblTest_dbf"
  
  MsgBox "File transfer to tblTest_dbf should be complete? (Cross our fingers)."

End Sub

and get the following error.

Run-time error '2507' - The Visual FoxPro Tables type isn't an installed database type or doesn't support the operation you choose.

And I have Microsoft Visual Fox Pro Loaded on my machine????

If anyone can help I would appreciate it as I seem to be finding information on Visual Fox Pro is very limited on this site and on the internet.

My aim here is too see a message box but it is not happening......

Robert88

 

[Robert88]

Thanks for everyone's help, LOL!!!!!

I managed to work it out from many different website, just thought I would share it with you.

Firstly my ODBC driver was not up to date, so I had to downalod the latest


http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx

Download the file VFPODBC.msi

Then after this was installed on my computer I then had to create an entry in my "ODBC Data Source", in this case I called it "Visual FoxPro"

Then with the following code;

Private Sub CmdReadDBF_Click()

     DoCmd.TransferDatabase , "ODBC Database", "ODBC;DSN=Visual FoxPro;UID=;SourceDB=D:\;SourceType=DBF;Exclusive=No;Background Fetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;DATABASE=", acTable, "test", "tblTest_dbf"
  
  
  MsgBox "File transfer to tblTest_dbf should be complete? (Cross our fingers)."

End Sub

I finally got my message box and of course tblTest_dbf table in Access.

Only one issue out of this is that when the tblTest_dbf is created, it creates it new. But if there is an existing tblTest_dbf it creates the new one as tblTest_dbf1.

Is there any way of overcoming this so that I only create a balnk tblTest_dbf and everytime I import it appends?

I look forward to any response on this one.

Robert88

 

[RV]

Is there any way of overcoming this so that I only create a balnk tblTest_dbf and everytime I import it appends?

Not with the method TransferDatabase itself.
What you can do in code in sequential order:

- transfer the data into a "temp" table tblTemp
- after the data has been transferred, run an INSERT statement that inserts the data held in your "temp" table into your tblTest_dbf table
- finally, delete tblTemp by using this code:

CurrentDb.Execute "DROP TABLE tblTemp;"

RV

 

[Robert88]

Magnificent

Hi RV,

Thanks for that.

Will give it a go if I have any problems I will let you know, shouldn't though.

Robert88

 

[Robert88]

Sensational

Hi RV,

Your a wizard, working perfectly!!!!!

Robert88

 

[Noel]

More help please

Robert888 -

I'm trying to do the same thing as what you have done, but I'm not having any success.

I downloaded the foxpro drivers and installed them. It seems that have set up the odbc...not 100% on this as there are several options. And, I tried a version of your code, but no luck

Do you have any more tips?

Thanks,

Noel