I currently have several databases that I have created in Access 2000. However, some of the databases are over 300 MB in size and have close to 100 tables. I'm looking for a more scalable database. SQL Server is one option, but is a rather expensive option. My question is this, can FoxPro handle large databases over 500 MB or more and up to 50 concurrent users? Is there a lot of interoperability with FoxPro and Microsoft Office? Or would I be better served to go the SQL Server route? Can I use the .NET framework such as C# with FoxPro as the backend? Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
FoxPro Scalability 1
- Thread starter Terpsfan
- Start date
MSDE is a consideration: It can handle about 10 concurrent connections (which, depending on transaction volume, would probably suit for 20 (heavy transactions) to 100 (light transactions) users... these are just guesses...) plus, it's free, and SQL Server compatible.
I don't think I'd recommend changing from an Access backend to VFP backend, but accessing the Access MDB's from VFP is a great approach... though a concern with so many users is network traffic: using MDB's requires file-sharing just like DBF's do. Here's where MSDE gives the benefit.
I don't think I'd recommend changing from an Access backend to VFP backend, but accessing the Access MDB's from VFP is a great approach... though a concern with so many users is network traffic: using MDB's requires file-sharing just like DBF's do. Here's where MSDE gives the benefit.
If you are primarily concerned about performance, the fastest data access is VFP Free tables.
However, if you truly want the power of a 'true' database system, I would look at MSDE or SQL. MSDE, I believe, is free but has limitations -- others can provide details.
Anyway, I do not suggest keeping the data in Access tables and accessing the from VFP unless there is some other reason why they need to be in Access tables. Look to moving the data to a SQL database (SQL Server or MSDE) or move the data into FoxPro tables.
FoxPro tables can easily handle the amount of data you mentioned and it can handle numerous concurrent users.
Jim Osieczonek
Delta Business Group, LLC
However, if you truly want the power of a 'true' database system, I would look at MSDE or SQL. MSDE, I believe, is free but has limitations -- others can provide details.
Anyway, I do not suggest keeping the data in Access tables and accessing the from VFP unless there is some other reason why they need to be in Access tables. Look to moving the data to a SQL database (SQL Server or MSDE) or move the data into FoxPro tables.
FoxPro tables can easily handle the amount of data you mentioned and it can handle numerous concurrent users.
Jim Osieczonek
Delta Business Group, LLC
-
1
- #4
Mike Lewis
Programmer
Omewga,
In my experience, VFP should be able to handle your needs comfortably.
can FoxPro handle large databases over 500 MB or more and up to 50 concurrent users?
Yes. I have clients who run applications on this scale happily in VFP. The only thing to watch for is the 2 GB limit on the size of an individual file (table or index).
Is there a lot of interoperability with FoxPro and Microsoft Office?
Yes. VFP can control Office applicatoins through COM. Also, Office can access VFP data via ODBC or OLE DB.
Or would I be better served to go the SQL Server route?
SQL Server has a number of advantages over VFP, but it is considerably more expensive. With SQL Server, you pay per user, while in VFP you pay per developer.
Wgcs mentioned MSDE. This is not really an option in your case. That's partly because MSDE is not a developer's tool; it is simply a run-time version of SQL Server. More importantly, it will never support 50 users.
Can I use the .NET framework such as C# with FoxPro as the backend?
Yes. This will give you full access to Fox databases and tables.
Personally, I would choose VFP for the front end as well. It is much faster to develop in that C#. Then again, I am biased in favour of VFP because I already know it. If you are starting from scratch, it can be quite daunting to learn.
Hope this helps. Come back if you have any more question.
Mike
Mike Lewis
Edinburgh, Scotland
In my experience, VFP should be able to handle your needs comfortably.
can FoxPro handle large databases over 500 MB or more and up to 50 concurrent users?
Yes. I have clients who run applications on this scale happily in VFP. The only thing to watch for is the 2 GB limit on the size of an individual file (table or index).
Is there a lot of interoperability with FoxPro and Microsoft Office?
Yes. VFP can control Office applicatoins through COM. Also, Office can access VFP data via ODBC or OLE DB.
Or would I be better served to go the SQL Server route?
SQL Server has a number of advantages over VFP, but it is considerably more expensive. With SQL Server, you pay per user, while in VFP you pay per developer.
Wgcs mentioned MSDE. This is not really an option in your case. That's partly because MSDE is not a developer's tool; it is simply a run-time version of SQL Server. More importantly, it will never support 50 users.
Can I use the .NET framework such as C# with FoxPro as the backend?
Yes. This will give you full access to Fox databases and tables.
Personally, I would choose VFP for the front end as well. It is much faster to develop in that C#. Then again, I am biased in favour of VFP because I already know it. If you are starting from scratch, it can be quite daunting to learn.
Hope this helps. Come back if you have any more question.
Mike
Mike Lewis
Edinburgh, Scotland
- Thread starter
- #5
Instead of learning a completely new language, I would like to continue with the .NET solution as a front end to Fox Pro. My reservations with Fox is that 1- Microsoft may end up dumping it 2- It's still a file server based db and could bottleneck the network 3- lack of security. Could I basically create the backend in Fox with stored procedures to update and insert records? If you don't mind addressing these issues I would much appreciate it.
The answer to your question is yes. You could use the .NET framework and then access the VFP tables. MS has just released a new OLEDB provided for visual foxpro and you would access it as recordsets. You can do the same with VBScript from the web.
However, It's hard to say for sure since we don't know your environment and specific needs, but my gut feeling is that I would move the data to SQL Server, MSDE, Oracle, or some other "true" database.
Go to for the latest VFP OLEDB driver. I believe there is a link to it from too.
As for your issues...
1. We have been hearing MS is going to drop FoxPro for 10 years now. In the past 5 or 6 years, MS has released version 5, 6, 7, 8 and they are working on 9. With all respect, the only people talking about MS dumping VFP are people not following VFP.
2. FoxPro tables operate on a tradition network environment, even if you move them to, let's say, a web-server. This will still be true if you use .net as a front-end.
3. VFP has added a lot security to the tables beginning with version 7.0. You have to set the security up on the database containers. In my opinion, most VFP programmers prefer SQL Server or MSDE to this option.
Jim Osieczonek
Delta Business Group, LLC
However, It's hard to say for sure since we don't know your environment and specific needs, but my gut feeling is that I would move the data to SQL Server, MSDE, Oracle, or some other "true" database.
Go to for the latest VFP OLEDB driver. I believe there is a link to it from too.
As for your issues...
1. We have been hearing MS is going to drop FoxPro for 10 years now. In the past 5 or 6 years, MS has released version 5, 6, 7, 8 and they are working on 9. With all respect, the only people talking about MS dumping VFP are people not following VFP.
2. FoxPro tables operate on a tradition network environment, even if you move them to, let's say, a web-server. This will still be true if you use .net as a front-end.
3. VFP has added a lot security to the tables beginning with version 7.0. You have to set the security up on the database containers. In my opinion, most VFP programmers prefer SQL Server or MSDE to this option.
Jim Osieczonek
Delta Business Group, LLC
Mike,
SQL Server does not necessarily have to be purchased on a per-user basis. You can get a server license which allows unlimited connections.
Craig Berntson
MCSD, Visual FoxPro MVP, Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"
SQL Server does not necessarily have to be purchased on a per-user basis. You can get a server license which allows unlimited connections.
Craig Berntson
MCSD, Visual FoxPro MVP, Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"
Mike Lewis
Programmer
Omega,
Instead of learning a completely new language, I would like to continue with the .NET solution as a front end to Fox Pro.
That makes a lot of sense.
1- Microsoft may end up dumping it
There is no evidence for that. On the contrary, Microsoft is putting a lot of effort into developing VFP.
2- It's still a file server based db and could bottleneck the network
True. SQL Server will give better performance, not because it is inherently faster (probably FoxPro is faster) but because it is a true database server, and can therefore substantially reduce network traffic.
3- lack of security
Also true. As Jim says, VFP's security model has improved in Version 7.0, but SQL Server is still much better in this respect.
Could I basically create the backend in Fox with stored procedures to update and insert records?
Yes. You can access VFP databases through ODBC or OLE DB.
Looking at it simply from the point of view of the back end, the choice between VFP and SQL Server probably comes down to this:
For SQL Server: Better performance through reduced network traffic; better security; better error recovery (can recover right up to the point of the failure, not just to the most recent backup).
For VFP: (Much) lower cost; simpler licensing issues; much easier to install and administer.
Let us know what you decide.
Mike
Mike Lewis
Edinburgh, Scotland
Instead of learning a completely new language, I would like to continue with the .NET solution as a front end to Fox Pro.
That makes a lot of sense.
1- Microsoft may end up dumping it
There is no evidence for that. On the contrary, Microsoft is putting a lot of effort into developing VFP.
2- It's still a file server based db and could bottleneck the network
True. SQL Server will give better performance, not because it is inherently faster (probably FoxPro is faster) but because it is a true database server, and can therefore substantially reduce network traffic.
3- lack of security
Also true. As Jim says, VFP's security model has improved in Version 7.0, but SQL Server is still much better in this respect.
Could I basically create the backend in Fox with stored procedures to update and insert records?
Yes. You can access VFP databases through ODBC or OLE DB.
Looking at it simply from the point of view of the back end, the choice between VFP and SQL Server probably comes down to this:
For SQL Server: Better performance through reduced network traffic; better security; better error recovery (can recover right up to the point of the failure, not just to the most recent backup).
For VFP: (Much) lower cost; simpler licensing issues; much easier to install and administer.
Let us know what you decide.
Mike
Mike Lewis
Edinburgh, Scotland
For some larger VFP projects, check out the following:
1) - Choose "Applications" on the left. Drill down for
details on listed applications.
2)
Rick
1) - Choose "Applications" on the left. Drill down for
details on listed applications.
2)
Rick
Mike's response:
Could I basically create the backend in Fox with stored procedures to update and insert records?
Yes. You can access VFP databases through ODBC or OLE DB.
is missing the reason that I was suggesting MSDE early on: the VFP ODBC driver and the VFP OLE DB driver are NOT client-server: The Driver becomes the local VFP program running using File-Sharing access to the .DBF files... it doesn't reduce network traffic.
In using MSDE, I was trying to imply (and thought it was clear) that VFP could (and should) be the front end. MSDE does not have a number-of-clients limit: it does have a number-of-connections performance degradation. And, in anecdotal reports I've read, this performance change is not even noticible at 20 simultaneous connections (which is more than enough connections for 50 users.... every user isn't connected continuously!)
As for the idea of a VFP back end, as Omega36 asks about, it is possible, See for some discussion of this. However, I didn't suggest it because this is nowhere near a proven, ready to use option.
Could I basically create the backend in Fox with stored procedures to update and insert records?
Yes. You can access VFP databases through ODBC or OLE DB.
is missing the reason that I was suggesting MSDE early on: the VFP ODBC driver and the VFP OLE DB driver are NOT client-server: The Driver becomes the local VFP program running using File-Sharing access to the .DBF files... it doesn't reduce network traffic.
In using MSDE, I was trying to imply (and thought it was clear) that VFP could (and should) be the front end. MSDE does not have a number-of-clients limit: it does have a number-of-connections performance degradation. And, in anecdotal reports I've read, this performance change is not even noticible at 20 simultaneous connections (which is more than enough connections for 50 users.... every user isn't connected continuously!)
As for the idea of a VFP back end, as Omega36 asks about, it is possible, See for some discussion of this. However, I didn't suggest it because this is nowhere near a proven, ready to use option.
Mike Lewis
Programmer
Wgcs,
the VFP ODBC driver and the VFP OLE DB driver are NOT client-server:
There's no disagreement here. The original question was whether VFP was sufficiently scalable for Omega's needs. I and others answered yes, but went on to discuss the advantage of using SQL Server to reduce network traffic. If I, or anyone else, implied that the VFP OLE DB provider or ODBC driver would reduce network traffic, that is definitely not the case, and not what I intended.
MSDE does not have a number-of-clients limit: it does have a number-of-connections performance degradation .... this performance change is not even noticible at 20 simultaneous connections
Yes, I've got similar anectdotal evidence. One of my clients runs an MSDE app with 15 busy users. Still, I'd be worried about trusting it with an app on the scale that Omega is proposing. Maybe he will be fine, but personally I'd spend rather spend the money on a full SQL Server system.
Mike
Mike Lewis
Edinburgh, Scotland
the VFP ODBC driver and the VFP OLE DB driver are NOT client-server:
There's no disagreement here. The original question was whether VFP was sufficiently scalable for Omega's needs. I and others answered yes, but went on to discuss the advantage of using SQL Server to reduce network traffic. If I, or anyone else, implied that the VFP OLE DB provider or ODBC driver would reduce network traffic, that is definitely not the case, and not what I intended.
MSDE does not have a number-of-clients limit: it does have a number-of-connections performance degradation .... this performance change is not even noticible at 20 simultaneous connections
Yes, I've got similar anectdotal evidence. One of my clients runs an MSDE app with 15 busy users. Still, I'd be worried about trusting it with an app on the scale that Omega is proposing. Maybe he will be fine, but personally I'd spend rather spend the money on a full SQL Server system.
Mike
Mike Lewis
Edinburgh, Scotland
VFP is definitely a step up from Access. Microsoft's website for Visual FoxPro General Reference has a section for Visual FoxPro System Capacities.
Maximum table size: 2 GigaBytes
Maximum records per table: 1 billion
Maximum fields per record: 255
Maximum characters per field: 254
Maximum characters per record: 65,500
(my calcs say 64,770 - could MS be wrong?)
14 Field types: Character, Currency, Numeric, Float, Date, DateTime, Double, Integer, Logical, Memo, General, Character (binary), Memo (binary), Picture
Maximum table size: 2 GigaBytes
Maximum records per table: 1 billion
Maximum fields per record: 255
Maximum characters per field: 254
Maximum characters per record: 65,500
(my calcs say 64,770 - could MS be wrong?)
14 Field types: Character, Currency, Numeric, Float, Date, DateTime, Double, Integer, Logical, Memo, General, Character (binary), Memo (binary), Picture
VFP is also faster than Access
And the SQL 2000 1 CPU license (unlimited users) is $5000
And the SQL 2000 1 CPU license (unlimited users) is $5000
Hi
You can try Firebird server. It has no problems with 50 users and is very solid. I've seen that it has a native .NET provider (no OLE DB). Being so small (the engine is only a 1.4 MB DLL, is also easy to use its database (one file, Access like) as a local data storage (the embeeded DLL) for offline data storing/processing.
I currently use it with a VFP frontend, the database is near 500 MB and the performance is very good.
You can try Firebird server. It has no problems with 50 users and is very solid. I've seen that it has a native .NET provider (no OLE DB). Being so small (the engine is only a 1.4 MB DLL, is also easy to use its database (one file, Access like) as a local data storage (the embeeded DLL) for offline data storing/processing.
I currently use it with a VFP frontend, the database is near 500 MB and the performance is very good.
- Status