Why doesn't A5v9 work w/ Visual Foxpro tables

[jjfcpa]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

I have not used VFP before but found this thread interesting since I would have thought that ODBC should just about answer any of the questions. But it appears from a quick Google that MS has a somewhat faulty ODBC driver for VFP. So Mark, getting back to your original question, I would guess that if MS can't even specify their own drivers correctly, you can't expect someone else to do the job. Since your comments don't really specify what it is that you want to do with A5 in your VPF environment we are left to guessing. If it is for report functionality or to get your data into a new environment, then you can probably use the ODBC driver as John has pointed out that this works for him. I think that this is about all you could expect from Alphasoftware to provide. However, if you are trying to intermingle the 2 environments then it appears you could be heading for a problem. I would personally try the ODBC drivers against my own data to see how it works. But from all the problems that I could read about, interacting with the data live could prove to be a problem.

This might lead you to understand why Alpha has been created around supporting DB structures that are more "standardized". With A5v9 you can mix data sources freely for those that are supported and with ODBC anything else - where the driver actually works, that is.

Here's a link to an interesting wiki on the driver:

http://fox.wikis.com/wc.dll?Wiki~VFP...er~SoftwareEng

HTH
Doug

Doug

In fairness to Microsoft, the VFP ODBC has not gotten any improvements or updates because they released the OLE DB connection for VFP tables; which provides direct access to VFP version 9.0 tables.

http://msdn.microsoft.com/en-us/libr...s0(VS.80).aspx

Obviously, there is no compelling reason for Alpha Five to provide native access to dbf files as modified by Microsoft, heck, even Excel will not access them. However, as I stated in my post, there are a lot of VFP developers looking for a new development tool and I really think that Alpha would get a lot of attention if they provide native access. Even if it were through their AlphaDAO connection.

This would allow you to use SQL SELECT (native VFP commands) against your native VFP tables. Since Alpha does not have pure SQL support for accessing dbf tables, this would be a very lucrative to VFP developers.

I know there has been a lot of talk about Advantage Database Server because they have an ODBC driver that supports access to VFP version 9.0 tables. The interesting thing to note is that it appears they may have fixed some of the problems with the ODBC driver that Microsoft released. I downloaded it and will test it tomorrow. Here's a link to it.

http://devzone.advantagedatabase.com...=14&Platform=6

[jjfcpa]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

Mark,



This stuck with me for a long time after I read it this afternoon. I used R&R a long time ago and uses Crystal reports more recently.

Have you worked with the alpha report writer? Don't count it out, it does a great job and you may find it has some things you don't have now.

Now, to the meat. It was hard leaving my old friend "Clipper", which I might add beat the pants off Foxpro in the old days. VB is better now, if you want to spend the time. I had choices with dos when leaving Clipper and one was FP, I chose alpha then and have never had a reason to look back.

WSo, when are you planning to make the switch? You cannot live on Mars and Venus at the same time. The distance is to great. You are on a dying world and (it seems) wanting to transfer to a thriving, very alive planet.

You ware very welcome in this world. Most all here came from another world somewhere and found a home. We brought a lot with us and I am sure you will too. We all share and aI am sure you will too.

Honestly, no one wants to leave a comfortable environment with a lot of accomplishments behind, but there is a time when it becomes necessary.

I would even bet you could use your R&R with alpha. Bet you won't need it though.

The only thing I liked about other report writers was the ability to make an exe for a report instead of it being embedded.


.

Dave

It seems like we have a lot in common except for the choice when Clipper failed to make it to the Windows world. I used Clipper before my switch to Foxpro when I needed to provide a Windows app. Back in those days, my users were questioning our longevity because we were still releasing DOS versions of our application well after Windows was gaining popularity.

By jumping to Foxpro 2.6, we were able to provide a DOS and Windows version that was source code and data file compatible. Pretty remarkable and it allowed our users to make the transition from DOS to Windows on their own terms.

Visual Foxpro is still a very sweet development tool and maintains a clear separation between UI (user-interface) and data. Alpha can't do that without a great deal of difficulty; in fact, I'm not sure it can be done. It seems somewhere in Alpha's history, they made a concerted effort to at least maintain feature compliant with what VFP offers. I've pretty much found most of what I had in VFP and then some.

When the database container was introduced in VFP version 6.0, we decided to maintain our data in free tables. Free tables are basically tables that don't belong to a database. Because the data is not in a database, you have to manage file open and closes, but you have the flexibility to do what you want, when you want, and how you want... admittedly at a price in terms of managing the UI.

I'm still trying to figure out if Alpha will fill the void that VFP is going to create in our development system. We're kind of to the point that we do not want to create any new apps in VFP because we know that it will have to be re-written in something else eventually. We have years of conversions a head of us depending on what we select as the tool of choice.

If Alpha provide native support for VFP tables, it would be a slam dunk, but right now, we have to work around converting our data as well as our codebase.

Right now, I'm trying to create my first Alpha app. It's a struggle to figure out the Alpha way of doing everything when I know VFP so well. In a few months, I'll have a better idea.

[DaveM]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

You will find it way easier than you think. I have made exe reports with Crystal and run them from alpha and it was very acceptible. I do not have the runtime rights to Crystal anymore and they are getting very pricy. It is also a difficult process now.

The beautiful thing about alpha is you can code in field rules, form/browse/layout/report level and also the code tab. Functions work very well and since you came from clipper, you will appreciate that. Most scripts can be made into functions which seem to execute faster than the scripts. Most everything coded in a form, layout, etc can be moved to a script which seem to work faster than at form, etc level.

Once you get the idea and a little practice, you will be on your way. Clipper with a 286 computer and a large program to a very long time to compile to an exe. we don't have compile in alpha which leads to other searches for some things. I start alpha from a simple exe created with vb6. It works very well. I hate selling a program with no exe.

There are so many ways to do things with alpha that people on here have discovered that I am not sure the programmers at alpha ever dreamed someone would do. Most of it is with no money spent, or very little.

Since alpha is bases on c, there are a few of their functions, I would swear are directly from c. I may be wrong though.

The ability to use native tables and/or other tables is fairly new and is a big plus. I am not a proponent of containered data which made me not be happy with access and to some degree vb & vfp. They can use the dbf and vfp does very well, but with vb it is a challenge.

Mostly alpha closes and opens your tables for you and takes pretty good care of the indexes. Get a little used to al[pha and work on query optimization. if done right it is like the rushmore(i think it is called-been years) speed.

In clipper, we shopped for functions to purchase, alpha gives us a tone of those. I have suggested a time or two to let us share (for a fee) our functions. Alpha is quick to do something for everyone if they see a programmer come up with a really good idea or method.

There are many areas where alpha can be improved, but not in customer service. They respond better than anybody I ever dealt with.

.

[jjfcpa]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

Hello All,

I have a vertical app that uses Visual Foxpro as the database.

Why can't A5 open the tables natively since they are dbf files?

It shows in A5 as an encrypted file.

Thanks

Mark Richter

Just wanted to let you know that we are now accessing VFP tables natively within Alpha via the ADO connection and utilizing the VFPOLEDB provider dll. That should already be installed on your computer if you have VFP installed or you can download from MS's website.

As soon as I have everything working the way I want in a form/dialog/script I will be happy to share it with you.

An associate in the office was having the same problem connecting to a VFP free table in .NET and when we figured that out, we tried the same thing in Alpha Five and it worked. Just making it pretty now.

[sparky06413]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

Just wanted to let you know that we are now accessing VFP tables natively within Alpha via the ADO connection and utilizing the VFPOLEDB provider dll. That should already be installed on your computer if you have VFP installed or you can download from MS's website.

As soon as I have everything working the way I want in a form/dialog/script I will be happy to share it with you.

An associate in the office was having the same problem connecting to a VFP free table in .NET and when we figured that out, we tried the same thing in Alpha Five and it worked. Just making it pretty now.

Thanks John,

I have tried to get the ADO working before on previous versions w/o success.

I would appreciate any help you can supply.

Mark

[John Gamble]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

John

I to would most appreciate any info on natively connecting VISUAL Fox Pro & Alpha 5. Have never had an success.

We puchased & had modified, an accounting system in VFP. (SourceMate or AccountMate).

Any attempts to access VFP have destroyed its indeces.

John

[jjfcpa]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

I wish I had the time to post step-by-step instructions, but I think if I get you started in the right direction, you'll make your way through it without any difficulty.

The process requires that you have the VFPOLEDB Provider installed. This can be downloaded from the Microsoft website... just Google VFPOLEDB. If you have a current version (8.0 or 9.0), you've probably already got it installed.

What I did was use the built in Genies in Alpha Five to connect to a file using ADO to get me started, and then modified the code to suit my needs.

Here are some abbreviated steps. If you have questions or run into problems, I'll do my best to give you some guidance.

1. Open any database that you have ...or... create a new one. You don't need any tables, just a new database.

2. Go to the Code tab and click on new to create a new script.

3. In the Code editor, right-click and select Genies and from the menu select ADO Record Set.

4. In the ADO Genie dialog, click on Build and in the Data Link Properties dialog, click on the Provider tab and select "Microsoft OLE DB Provider for Visual Foxpro". If this doesn't show up on the list, you don't have it installed.

5. Click on "Next" to go to the Connection tab. You then need to click the ellipses buttons to select a DBC or free tables that you want to connect to. I've experimented with both and they both work. Keep in mind that if you select free tables, even though you have to select a file, you can change the file that you want to connect to later. If you select DBC, you will be able to select any table in the DBC later.

6. Click on "Test Connection" to make sure you have your settings correct. Then click on the Advanced tab and check the "Read" checkbox. Then click OK.

7. The connection part is done, you then need to type in an SQL Select statement before you can insert the generated code into the code editor. In my case, I entered "SELECT * FROM CUSTOMERS" since I wanted to open a VFP customers.dbf.

8. Then click the "Insert Code in Script Editor" before closing this dialog. You will then see the code (which includes the connection string) to use the VFPOLEDB provider. At this point, what I did was copy everything except the last two commands (which close the table and the connection) to the Interactive tab of the Code editor. I highlighted it and then clicked on the Run button on the toolbar.

9. At this point, your table should be opened and the only tricky part is accessing the data in the fields. In my customer table there is a field called "Company", so to display the contents of this field in the interactive tab I then typed: ? flds.item("company").value

In our particular example, we needed to find a record in our table to compare it to the information the user submitted, so we just modified our SQL Select statement and added a "where" clause.

If anyone figures out the correct commands to use to move the record pointer to another record or do a locate, I hope they post it here.

By the way, if you want to generate a form showing selected data from a table, instead of running the Genie for the ADO Record Set, select the Xbasic Script Genie and select the option to "Display an xdialog using ADO data". The procedure is similar but you'll end up with code to generate a form that will display selected fields from your table and will also include (as an option) record navigation controls. Very slick.

So now I can easily access VFP tables and get data out of them as the need arises.

[John Gamble]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

John
[
So now I can easily access VFP tables and get data out of them as the need arises.[/QUOTE]

Read you post with much interest. Your last line appears to inicate READ ONLY access ?

John

[jjfcpa]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

John
[
So now I can easily access VFP tables and get data out of them as the need arises.

Read you post with much interest. Your last line appears to inicate READ ONLY access ?

John[/QUOTE]

Not true. I just hadn't figured it out yet, but Kristy in my office already did. You need to use the "execute" command of the connection string to do an SQL INSERT INTO command to update the VFP tables. It works just fine.

Look up ADO in the help file and it is documented in the section on updating tables.

[John Gamble]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

John

Thank you for the info. I will do some experimenting.

John

[pboomwork]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

Ran into this problem tonight. Found what looks to be a solution. Figured I would post to this thread in case others where faced with the same problem and found this rather old thread.

Found this thread, on a Visual ForPro Wiki. Look at the bottom where it recommends using the Advantage ODBC drivers. I found I only needed to install the ODBC drivers not the full advantage package as indicated in the wiki.

To allow updates of the FoxPro data from Alpha Five I needed to change the folowing in A5.

Edit the Alpha Five SQL Syntax for Updates in the Active-Link Table
Definations to the following;

SQL for UPDATES statement is: Custom
Native SQL
Where clause only
Where clause is based on: primary Key Columns

Configure the Advantage SteamlineSQL ODBC Driver Setup as follows

Table Type: Visual FoxPro
Available Server Types: Local Server (ALS)
Show Deleted Rows (not checked) (Note 1)
Trim Trailing Spaces (checked)

Note 1
This allows A5 to set the delete flag for records. They will appear to be deleted in A5 however A5 can not pack the tables and this would need to be done from Visual FoxPro.

[Jeff Fried]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

Paul,

This is interesting in that it uses the SQL approach in A5 while my focus has always been on the native DBF format.

Your approach is going in the opposite direction from what I was looking for. Your focus is on updating a VFP database from A5. I never have a situation like this. Rather, my main focus is in the A5 world, and I run a VFP procedure out of A5 to perform data manipulations and the like. The two areas where I need to create workarounds are in the index updates and certain operations around memo fields. I can write back to a file in dBASE III format (A5 doesn't read VFP formatted DBF's) if I need to at the end of the procedure, but I don't think I can reliably update the index file container, and there are times when I want to work with data in memo files -- this too is tricky.

Thanks for your observations regarding viewing VFP as a remote database to be manipulated by A5. If I ever run into a need to use A5 as a front end to a fully-developed VFP database, this will come in handy.

Jeff

[StevenMcLean]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

Visual FoxPro locks the tables with the exception of "Free Tables" for RI. This is done in the table header. You can modify the header edit the data, and change the header back. You can not do this while the VFP app is running and you could corrupt the .DBC if your changes violate RI.

Steve

[JAF]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

I have a VFP6 application and I want to use A5 for custom reports. I haven't figured out how to deploy the report to another computer yet and get it to work with any database on the other computer. I have the Run Engine installed on the remote computer. I have even used the exact same file/folder structure.

What has to be done to get this to work? I'm using active-links to access the Foxpro data.

[StevenMcLean]

Re: Why doesn't A5v9 work w/ Visual Foxpro tables

If it is only reporting, why not use R&R. It will read from the Data Dictionary. This may be easier, and less dangerous.
Steve