473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

MS Access Front-Ends with MySQL databases Path-Forward assistance

I have an Access 2003 db that I've designed to run inventory for a sports memorabilia business. I'm realizing that I should get this data onto the web and run a storefront via MySQL & PHP.

My concerns are that the current db has multiple Append Queries to add stock and transacted items. If I were to utilize an ODBC connection from Access to MySQL, would I be able to run these queries and update source tables in the Access db and have that reflected in the MySQL database on line?

Secondly, the inventory quantities are a query in the Access db, based on this format: Buy_Qty: Sum(IIf([Trans_Type]="Buy",[Qty],0)). The inventory on-hand is then the sum and subtraction of the relevant actions toward quantity on-hand.

How easy is it to keep a query in MySQL that would duplicate this data and be used when a web-user wants to search for inventory via a to-be designed PHP form?

I'm sorry if these questions aren't very strong, I'm studying the feasibility of moving online in this manner and I think the best way forward would be to use the front-end I already have and allow the backend to exist as MySQL. Any thoughts on if that's wise?
Sep 4 '08 #1
1 2138
coolsti
310 100+
I have an Access 2003 db that I've designed to run inventory for a sports memorabilia business. I'm realizing that I should get this data onto the web and run a storefront via MySQL & PHP.

My concerns are that the current db has multiple Append Queries to add stock and transacted items. If I were to utilize an ODBC connection from Access to MySQL, would I be able to run these queries and update source tables in the Access db and have that reflected in the MySQL database on line?

Secondly, the inventory quantities are a query in the Access db, based on this format: Buy_Qty: Sum(IIf([Trans_Type]="Buy",[Qty],0)). The inventory on-hand is then the sum and subtraction of the relevant actions toward quantity on-hand.

How easy is it to keep a query in MySQL that would duplicate this data and be used when a web-user wants to search for inventory via a to-be designed PHP form?

I'm sorry if these questions aren't very strong, I'm studying the feasibility of moving online in this manner and I think the best way forward would be to use the front-end I already have and allow the backend to exist as MySQL. Any thoughts on if that's wise?
I just helped a colleague set something up which may be similar to what you want to do.

In her case she wants to use Access as a front end for an application, but connect to MySQL as the datasource. I don't think this a good idea as I would suggest to make the application front end in C# or make it web based using PHP, but she insists on using Access. But the use of the MySQL database on a remote centralized server rather than the Access databases is a good idea since this allows multiple user access, etc.

Anyway, we just downloaded the ODBC connector from MySQL and installed it on her machine. This allows her (and any user of her application) to link her Access front end consisting of various forms to the MySQL database. In her Access forms she defines various queries that are carried out when certain buttons are pressed. And these queries are written in the MySQL query syntax. In some cases she tells me she had to create views in the MySQL database to get some things working.

The point is, I think that you will need to change the queries that you have already in Access to be in MySQL syntax.

I am not sure what you want to do with this duplicating data with PHP. If you put your data in a MySQL database and use this as datasource for your Access part, then there is nothing stopping you from also creating a PHP application that, independently from the Access application, also has access to the database. In my PHP scripts, if I have tasks that require mutiple queries to carry out, I lock all affected tables during these transactions to lock out any other user (coming from Access or PHP) during the carrying out of the script.

Here of course I am talking about linking your Access to the MySQL database, as opposed to doing an Access copy of the MySQL database into a local Access database.
Sep 5 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

63
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy...
3
by: icb | last post by:
Hi I have a front-end/back-end setup. The front-end utilises all unbound forms populating the back-end via the code I have written. All fine so far. Prior to splitting the database I ran the...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
14
by: D | last post by:
Hey guys- not sure where this post fits in, so I cc'd a few other groups as well- hope you dont mind... I have someone creating a database for me in Access 2000 (or is it called XP?). When it's...
35
by: deko | last post by:
Do I get more scalability if I split my database? The way I calculate things now, I'll be lucky to get 100,000 records in my Access 2003 mdb. Here some math: Max mdb/mde size = 2000 x 1024 =...
4
by: BerkshireGuy | last post by:
Our IT department wants to place our Access 2000 tables on an SQL server due to the fact the tables are quite large. With that said, can we still use the Access queries or do we have to do...
22
by: Jordan S. | last post by:
SQL Server will be used as the back-end database to a non trivial client application. In question is the choice of client application: I need to be able to speak intelligently about when one...
64
by: John | last post by:
Hello there, Im cursing my place of employment...and its taken me a month to realise it... The scenario: Ive just stepped into a role to migrate an access database to VB.Net. The access...
10
by: Les Desser | last post by:
In article <fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes Thank you for that. It was very...
14
by: Brian Nelson | last post by:
Sorry for the long post, I've tried so hard to solve this one but I'm just stuck! We've had a multiuser .mdb file on a network share for years. I know it's not ideal, but it's worked well from...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.