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 Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Auto convert MS Access queries 2
- Thread starter Domino2
- Start date
-
1
- #2
DonQuichote
Programmer
- Nov 9, 2001
- 980
As you can use user-defined functions and even reference forms and reports in Access queries, this would be extremely hard to do. However, if you have standard SQL queries, most of them will just run.
Events are even harder: they may be Access VBA or even macros. There is no equivalent of that in MySQL as MySQL knows no forms or reports. And therefore no events.
You will have to define the word "database" here. For MS-Access, this can mean a a front-end application with forms, reports and modules. For MySQL, this is a back-end only.
If you change your queries to "pass-through" queries, you can leave the front-end as it is and use MySQL as the backend. You'll have to use MySQL-understandable SQL though, and you cannot use your VBA functions anymore.
+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
Events are even harder: they may be Access VBA or even macros. There is no equivalent of that in MySQL as MySQL knows no forms or reports. And therefore no events.
You will have to define the word "database" here. For MS-Access, this can mean a a front-end application with forms, reports and modules. For MySQL, this is a back-end only.
If you change your queries to "pass-through" queries, you can leave the front-end as it is and use MySQL as the backend. You'll have to use MySQL-understandable SQL though, and you cannot use your VBA functions anymore.
+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
- Thread starter
- #3
Many thanks, sounds like a bumpy ride ahead. My Access database is a front end/backend, ie split. At the moment it is used by one user group, but another group wants to access it, but do not have a common server. They are asking me to consider putting it on a web server. The idea of converting all forms into MySql looked a time saving way out, but if MySql does not recognise forms then where do you go from there. Can anyone steer me in the right direction to get something off the ground?, ie any demo sites. Thanks
DonQuichote
Programmer
- Nov 9, 2001
- 980
If the whole group is on the same network, just put your backend on the server somewhere where everybody in the group can access it.
If some of the users are abroad (working from home perhaps) they could set up a VPN to get on the network.
If you switch to MySQL, not that much will change: the server containing MySQL should be open to the group, and most probably closed to anyone else. A web server is therefore not a good idea.
My advice is therefore to stick with Access for now and switch to a database server as backend when you need it (for scalability, say).
+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
If some of the users are abroad (working from home perhaps) they could set up a VPN to get on the network.
If you switch to MySQL, not that much will change: the server containing MySQL should be open to the group, and most probably closed to anyone else. A web server is therefore not a good idea.
My advice is therefore to stick with Access for now and switch to a database server as backend when you need it (for scalability, say).
+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
-
1
- #5
- Thread starter
- #6
Thanks both. So a web server is not a good idea! I did a quick look into VPN, and will have to study it more.
Yes the database is a split front/backend system. Problem is two different companies want to get access to it both in reading/writing. They don't have a common server. I did think the idea of keeping the front ends in Access and putting the tables as a backend somewhere in MySql. So I have two visible obstacles, one being Access talking to MySql (query conversions) and the other is where do I put MySql backend.
If only this was discussed in the beginning!!!
Bit confused on "linking the front tables to the mysql backend ? " Can you enlarge on that for me. Thanks for the help, really suffering here.
Yes the database is a split front/backend system. Problem is two different companies want to get access to it both in reading/writing. They don't have a common server. I did think the idea of keeping the front ends in Access and putting the tables as a backend somewhere in MySql. So I have two visible obstacles, one being Access talking to MySql (query conversions) and the other is where do I put MySql backend.
If only this was discussed in the beginning!!!
Bit confused on "linking the front tables to the mysql backend ? " Can you enlarge on that for me. Thanks for the help, really suffering here.
DonQuichote
Programmer
- Nov 9, 2001
- 980
Your front-end has "linked tables": links to tables in the backend. If you install MyODBC, you can put those tables in MySQL.
+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
- Thread starter
- #8
Many thanks for your help. I have done a search on MyODBC and will follow the route. I am slightly confused at this early stage which might get explained later. The changes at location 1 are saved to the original backend tables at location 1 (ie company 1) to the common MySql tables somewhere. However at location 2, how do their backend access tables get updated from the MySql tables. Am I getting it wrong somewhere, or is there a logical answer? Thanks
DonQuichote
Programmer
- Nov 9, 2001
- 980
You would connect to the same server from both locations. This can be done with MySQL. For security, you can use SSL connections (never done this) to the MySQL server, a VPN tunnel or an SSH tunnel.
If you run MySQL on Windows, a VPN is probably the easiest way.
If you run MySQL on Linux, SSH is probably easier.
A VPN (Virtual Private Network) is a way to securely connect to (usually) a corporate network over the big bad internet. It is a typical setup for working at home.
SSH is the Secure SHell protocol and application, that allows a lot of things securely that were done insecurely in the past (remote login, FTP, etc.) and allows you to forward TCP ports through an encrypted connection.
I suggest you talk to your system administrators about this.
+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
If you run MySQL on Windows, a VPN is probably the easiest way.
If you run MySQL on Linux, SSH is probably easier.
A VPN (Virtual Private Network) is a way to securely connect to (usually) a corporate network over the big bad internet. It is a typical setup for working at home.
SSH is the Secure SHell protocol and application, that allows a lot of things securely that were done insecurely in the past (remote login, FTP, etc.) and allows you to forward TCP ports through an encrypted connection.
I suggest you talk to your system administrators about this.
+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
- Thread starter
- #10
Yes it works bi-directionaly. I havn't got access on my PC at the momenet but there used to be a thing called the link table manager.
I've done a quick google and the following look interesting
and some code that might be usefull
So to summarise each PC that has access on will need the mysql ODBC driver instaling.
It will then need configuring to the correct data base To do this you wil need the database name (which I presume you will create), a user name and password and the server name it resides on.
You need to then fire up the link table manager and go from there. As I say I don't have access on my PC anymore so can't type anything for you. I do have it on my laptop at home so could help from there but I think you'd be better opening up a new query on the access forum and place a link back to here. I'm sure they will be able to point you in the right direction as long as you get the mysql bits sorted out.
Nedd anything else come right back
I've done a quick google and the following look interesting
and some code that might be usefull
So to summarise each PC that has access on will need the mysql ODBC driver instaling.
It will then need configuring to the correct data base To do this you wil need the database name (which I presume you will create), a user name and password and the server name it resides on.
You need to then fire up the link table manager and go from there. As I say I don't have access on my PC anymore so can't type anything for you. I do have it on my laptop at home so could help from there but I think you'd be better opening up a new query on the access forum and place a link back to here. I'm sure they will be able to point you in the right direction as long as you get the mysql bits sorted out.
Nedd anything else come right back
- Thread starter
- #12
- Thread starter
- #13
Gone down the road of linking Access to MySql, failing miserably. I have tried exporting tables via ODBC Connector in MySql, as well as a program called AccessToMysqlPro. When I look at my tables in MySql, every table had a warning message sayin two fields contain the same data and one should be removed. The fields are the primary keys, so I deleted what was described as Index fileds. Then after reading further I added a timestamp field to each table. When I thought it all looked okay in the MySql table, I went back into Access and added the MySql tables (using link tables) and entered what I thought were identifiable fields. However if I go into Access and change a record, it's not passed into MySql. Why are things so flippin difficult, any ideas, thanks
This might be a red herring but I recall that Access tables need a unique key to operate correctly, I wonder if this is passed onto any linked tables, so you might have to put an auto incremenet column and that becomes the primary key (although you wont use it as such). I wint be able to get to my PC tonight but I'll have a look on Tuesday and get back to you if that's any help?
- Thread starter
- #15
Many thanks. I followed your help on this. I transferred all the tables into the MySql database using AccessToMySqlPro. It messed it up the first time as it created indexes everywhere. The vendor then suggested unclicking generate indexes and it went in okay. I used the link manager and brought the tables into the database. It added a 1 next to each name. I then could not fathom out why my MySql table data did not get updated, as I thought you kept both Access and MySql tables in the Access database. I had seen no reference anywhere to delete the old tables. I did it by trial, took off the trailing number after the new table names, and hey presto works perfectly. So thanks again for all your help on this.
- Status
Similar threads
- Locked
- Question