Need To Pull Data from a Table in Access into Foxpro 1

[Allwolfpackfan]

I Need To Pull Data from a Table in Access into a FoxPro table or cursor. I've researched online and cannot find any info for this. Has anyone been able to accomplish this?

 

[Olaf Doschke]

VFP can query anything with an ODBC driver or OLEDB PRovider. Access even offers both.

In VFP itself the smallest code is using an SQL Passthrough 3-liner:

h=SQLConnect("DSNNAME")
SQLExec(h,"SELECT * FROM theTable","crsVFPResult")
SQLDisconnect(h)

There's more to do if you want to handle probable errors. But mainly that's it.

Then you may want to use SQLStringConnect instead of SQLConnect and use a connection string which is shown in variations for the several Access versions, drivers or providers at

http://www.connectionstrings.com

Besides that, quite similar to VFP the installation of Access does not necessarily install Access database drivers, as those are only necessary for third-party developers. The VFP ODBC driver and/or OLEDBPRovider is not part of the main VFP installation, not a prerequisite installation but one of the updates and additional optional components the setup finally points to. And likewise, you need to have Access installed with developer extensions. That could be a root of the problem why you don't find ways to connect. But ODBC Drivers are easy to install aftermath, I'm sure you'll find the one you need for your Access. another problem might be that you need 32bit drivers, as VFP isn't 64bit. Installers for x86 are not running on today's all 64bit Windows but you have to carefully look, x64 installers include "pure" ones only containing 64bit drivers but also "wholesome" ones with both 64bit and 32bit drivers for x64 Windows. Without knowing details I can't point you at something, but in general, pick the largest 64bit setup of developer extensions or drivers. At least there is nothing like 64bit files, just that Access files can outgrows 2GB in 64bit Access and then VFP will have problems accessing them even with 32bit drivers.

If you just have an office installation from your company including Access, you might not have the possibility to add this and you might not have any drivers VFP could use to connect.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TamarGranor]

I wrote about how to do this a long time ago:

http://www.tomorrowssolutionsllc.com/Articles/Importing Access Data.PDF

Tamar

 

[Allwolfpackfan]

The Access version we have is a normal Office installation. It's just a stand alone database with an accdb extension. There are no passwords or user ids'. I don't need to recreate the entire database but rather just pull data from one of the tables. It's a simple table with 4 fields (1 date, 1 text, 2 numeric). I was hoping to be able to append data from it to a FoxPro table with the same field names and data type. Are there any simple commands to do this? Tamar I looked at the link you posted but it's way over my head. I was hoping for something a lot simpler. I am used to appending Excel, csv's, dbf's etc into FoxPro but have never pulled anything from Access.

 

[Mike Lewis]

Have you considered exporting the data from within Access to CSV format, and then appending it to your DBF within Foxpro?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Allwolfpackfan]

Hi Mike, yes I thought about that. Actually tested exporting it to a dbf and it pulls fine into FoxPro. The problem is I don't see an easy way to automate this. Access allows me to save the export definition but the only way to automate it is through an Outlook task....and that requires the person to open the Outlook task reminder (when it generates) and they have to manually push a button to do the export where as my goal is to completely automate everything. I thought about using VBA and a module macro.... which would work but then it becomes a 2 step process vs just pulling the data into FoxPro as needed.

 

[Olaf Doschke]

Well, look back at the three liner above. Once you have the crsVFPResult - that's the alias name of the workarea aka Curso alias name.

Then you can USE some.dbf and APPEND FROM DBF("crsVFPResult") to add the Excel data to the dbf. Or simply SELECT crsVFPResult and COPY TO somother.dbf

Then once you have that going compile the code to an EXE and it's repeatable. I don't know what simpler thing you need.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Allwolfpackfan]

Hi Olaf, your level of programming is far above mine. I am very good with FoxPro but have limited knowledge when to comes to pulling data from different data sources. The access database I need to pull from is located in C:\Programs\Coolant\Cool.accdb and the table I need to pull is called DATA
I have an empty FoxPro table called Coolant.dbf that has the same structure as the DATA (but it sounds like that is not needed).

 

[Mike Lewis]

Allwolfpackfan,

I understand that you want to automate the process, and I can see the difficulties for you in doing that on the Access side.

But did you Access can directly export to dBASE III format? According to this article:

https://support.office.com/en-us/article/export-data-to-dbase-ec7e1708-d6b3-4b11-834b-bf3377b32b1e,

it should be a simple menu selection:

External Data -> Export group -> More -> dBASE File

I would have thought that could easily be automated in Access with a trivial macro. You could then assign the macro to a toolbar button or a keystroke, giving the user an easy way of achieving the task.

The point is that dBASE files are DBF files, and VFP can read and update them without any special action. Once you have your Access table in a DBF, you can use VFP to extract the fields and records that you are interested in.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Allwolfpackfan]

Hi Mike, yes Access can export to dBase III and it pulls fine into FoxPro. I setup an External Data, Saved Exports in Access and that's when I saw they use an Outlook task to automate it.... but it still requires someone to manually execute the task (when Outlook reminds them it's due)….. so still not automated. I could probably create a VBA macro to do it but it's still a 2 step process where as I would like to keep it to a 1 step pull (if possible). I just can't believe how difficult Microsoft makes this. Microsoft is going backwards now so they can sell more of the newer software it seems.

 

[Mike Lewis]

I don't think you can blame Microsoft. Microsoft do provide a tool that will enable you to pull data from Access into VFP. It is called ODBC, and it is precisely the tool that Olaf suggested at the start of the discussion. I agree that it can look a bit daunting when you first see it, but it's really not that difficult.

I suggest you do some reseach into using ODBC within VFP. Or, if that doesn't appeal to you, try to find someone who will do the job for you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Allwolfpackfan]

I 100% blame Microsoft. They eliminated things we've been doing for 20+ years now and the only explanation is so they can sell newer software. Have you noticed how they degraded the Task Scheduler in Win 10? Even Microsoft people had no idea what Microsoft had done. Regardless, I just got it to work but only after I saved the Access database to a 2003 version with the .mdb extension. This works great! but I have not found a driver that will work with the new version of Access that has the accdb extension.

 

[Olaf Doschke]

The major job is to define a DSN, but that isn't even programming, it's using the ODBC data sources administraotr. Then you have what the code I posted needs.

And to compile an EXE in VFP is clicking a build button. You need to create a project, write the code I gave into a prg, which autmoatically becomes the main prg of the project and click build. That's all.

Finally, if the file location of the accdb change all that needs adjustment is the data source DSN, the exe can stay as is. So that's not much to do.

So, let's say you have put up a DSN with the ODBC Data Source administrator of Windows (You need to use the 32bit version, as this needs to be seen from a 32bit VFP process) and then my code, here's the line to produce a dbf added:

h=SQLConnect("YourAccessDSN")
SQLExec(h,"SELECT * FROM Data","crsVFPResult")
SQLDisconnect(h)
COPY TO c:\targetpath\accessdata.dbf

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Allwolfpackfan]

Hi Olaf, Agreed. The FoxPro side is a piece of cake. Finding a DSN driver that works with an accdb format is where I am currently stuck.

 

[Olaf Doschke]

If you google DSN driver you won't find something DSN is for Data Source Name, the drivers used are ODBC drivers.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Allwolfpackfan]

SUCCESS!! I was able to locate the needed odbc driver for Access accdb format. Here is a link to it.

https://www.microsoft.com/en-US/download/details.aspx?id=13255

I Downloaded the file, installed it, added a new DSN for that Access database to the list of odbc DSN's.... then tested and it works fine. Here is the final FoxPro code.

h=SQLConnect("COOLANT")
SQLExec(h,"SELECT * FROM data","crsVFPResult")
SQLDisconnect(h)

COOLANT is the name of the DSN tied to the Access database and data is the name of the table I needed.
The only issue I see is the Character field imports as a Memo field but that's easy to convert back to character.
Thanks to all of your for your help.

 

[Olaf Doschke]

Good. there are some options you have when using a remote view or a cursor adapter to specify the result data types.

As you define the Access data you could also change that. I assume the Access data type is Text with 255 character length, which is just 1 too much for VFP char fields, but 255 is the default in the Access table designer when you pick Text as the field data type. Just change that to 254 and you get a char field in the result cursor or dbf.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Glad you have managed to get it working. As we said, ODBC might look daunting at first, but it is not all that difficult.

Now that you have made a start, you will find a lot more doors open to you. You would do well to read the topics in the "remote data" section in the VFP Help file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, one of the next steps you could do now the DSN is working is what I said earlier:

Then you may want to use SQLStringConnect instead of SQLConnect and use a connection string which is shown in variations for the several Access versions, drivers or providers at www.connectionstrings.com

From the link you posted I assume you use Access 2010, then this would be specific to that access version:

https://www.connectionstrings.com/microsoft-access-accdb-odbc-driver/#access-2010

You'll know best what fits your situation with or without an mdw file

You see why I first suggested the ODBC data source administrator, as that'll have dialogs asking you the information you here need to know how to specify with which specific names.

The advantage is you then can take the EXE (and vfp9r.dll runtime of course, and the accdb file) to anywhere else, adapt the path to the accdb and have your daily data extract anywhere.

In the long run, I also agree with Mikes advice, instead of storing the data into a dbf and acting on that with further FoxPro code you can work on the accdb backend.

And the further steps could be data stored in MSSQL, as a common database for both the access application and your FoxPro add on. But I don't know how far your influence goes in that or if that's an access database from a third party or whatever not under your influence. It'll just be fortunate for any company, if data of anything involving more than one employee and workflow is centralized, of course.

Bye, Olaf.




Olaf Doschke Software Engineering

https://www.doschke.name

 

[Nro]

Glad you found a solution to your problem. Connecting different type of database is always a challenge.

I always found accessing data thru ODBC is a little bit complicated. You have to install the driver, configure the System DSN (32 or 64 bit) … etc. I you have a different Access version (2003 / 2007 / 2010 …) you have to re-test everything.

I’m working with a reverse approach: from MS Access, I connect to my VFP tables thru ADODB connector. I have an Access form with a timer (automation), and some very simple VBA Code …

Dim loRstVFP As New ADODB.Recordset
loCntVFP.ConnectionString = "Provider = vfpoledb.1;UID=;" & _
    "Data Source=P:\Objitech\APPS\DEVELOP\Data\Develop.Dbc;" & _
    "Collating Sequence=MACHINE;" & _
    "Exclusive=no"

loCntVFP.CursorLocation = adUseClient
loCntVFP.Open loCntVFP.ConnectionString

lcSelectVFP = "SELECT commPendr.* FROM commPendr "

' Opening VFP cursor (commPendr table)
loRstVFP.Open lcSelectVFP, loCntVFP, adOpenKeyset, adLockOptimistic

After that, it’s easy to open a cursor from the Access table, scan thru the records, see if it exists in VFP and so on.
Hope it help.
Bye
Nro