A few things to consider. One is that the "text" data type in Access/JET is equivalent to "varchar" in SQL [as the author said]. The "memo" type in Access is equivalent to SQL's "text" data type.
Also, be aware that Access/JET is very greedy with table data. Even when you make a query/view that selects a few records from a table, Access will pull the entire table across the net and then display the few records that you want. To avoid this, you will need a view in MySQL to limit the data for you, or hope that MySQL has stored procedures implemented by the time you get there.
Discussion on:
View:
Show:
That's one of the biggest problems with trying to use Access as a server database instead of a local data store. For most processes, you'll have to lob the whole file (deleted records, stored procedures and all) back and forth across the network.
Even on a high bandwidth LAN, one user can gum up the whole works if they bounce that mdb back and forth too much.
Even on a high bandwidth LAN, one user can gum up the whole works if they bounce that mdb back and forth too much.
I'm sorry if I'm mistaken, but can't you open a table in Access as a dynaset, thereby limiting what portions of the tables are pulled across the network?
I know you can do this locally, but isn't this also possible in remote databases?
I know you can do this locally, but isn't this also possible in remote databases?
Dynasets were VB5 and earlier. It is possible to do all kinds of things with code to minimize the amount of data that gets lobbed back and forth over the net. However, if you open a local MDB with links to external tables, the entire contents of the table will be transmitted from the external database to the local app instance.
>However, if you open a local MDB with links to >external tables, the entire contents of the >table will be transmitted from the external >database to the local app instance.
This is true only if the remote database is a Jet
database. Jet is not a server therefore all processing is done on the client. With a true '
datbase server like MYSQl or Oracle or SQL Server
the processing is done on the server and only the result set is transfered to the client.
This is true only if the remote database is a Jet
database. Jet is not a server therefore all processing is done on the client. With a true '
datbase server like MYSQl or Oracle or SQL Server
the processing is done on the server and only the result set is transfered to the client.
You are right. Opening a recordset with either DAO or ADO and the right combination of Where clause will act exactly like a standard SQL statement sent to a database server (At least with SQL-Server). It is quite probable that the same process will occur if your connection is to Oracle, MySQL, DB2.
Of course if a user click on a table icon which links back to an SQL-Server and move to the last record it is absolutely true that all the table records will be pulled back to the station (with a1 million records table, this is not the right thing to do).
However, contrary to general belief, Access is quite capable of optimizing query process if the query contains selection criteria such as Like, =, etc. If possible Access (either via JET, ODBC or OLEDB) will use an existing index and then use the index keys to pull back to the station the corresponding records from the tables. So it is not always true to say that Access brings back the whole table over the network. It really depends how you query it.
I often use pure Access query (both with JET and ODBC) because I like the visual way of creating them, but this is not the most optimized way. Using DAO or ADO recordset and a good combination of Where statement is probably the best way to optimize Access as a front end.
Of course if a user click on a table icon which links back to an SQL-Server and move to the last record it is absolutely true that all the table records will be pulled back to the station (with a1 million records table, this is not the right thing to do).
However, contrary to general belief, Access is quite capable of optimizing query process if the query contains selection criteria such as Like, =, etc. If possible Access (either via JET, ODBC or OLEDB) will use an existing index and then use the index keys to pull back to the station the corresponding records from the tables. So it is not always true to say that Access brings back the whole table over the network. It really depends how you query it.
I often use pure Access query (both with JET and ODBC) because I like the visual way of creating them, but this is not the most optimized way. Using DAO or ADO recordset and a good combination of Where statement is probably the best way to optimize Access as a front end.
The biggest qualifier here is who handles the where clause.
If it's a pass-through query, then the server handles the where clause and passes only the data required by the client.
However, If it's a standard query on an attached table, DAO/Jet gets the entire table's contents from the server, and applies the where clause locally.
If it's a pass-through query, then the server handles the where clause and passes only the data required by the client.
However, If it's a standard query on an attached table, DAO/Jet gets the entire table's contents from the server, and applies the where clause locally.
Enjoyed the article. I've got MySQL on my hosting provider's server, and this might be better in some ways than phpMyAdmin. I'm also not as savvy w/ PHP as I am w/ VBA/Access, so I could cheat with this technique : )
It seems that this would sendplaintext MySQL passwords in the clear. Does the MySQL ODBC driver do anything to protect you from that? I'm sure that any credential can be cracked, but sending a plaintext password simply invites mischief. Any ideas...?
It seems that this would sendplaintext MySQL passwords in the clear. Does the MySQL ODBC driver do anything to protect you from that? I'm sure that any credential can be cracked, but sending a plaintext password simply invites mischief. Any ideas...?
Is ODBC the best connection you can do with MySQL?
If so, I suggest that what you can accomplish with ACCESS as a front end without bogging down on a slow network (i.e. modem at 56 Kbps) is much too limiting for most applications.
Is there no OLEDB connectivity?
If so, I suggest that what you can accomplish with ACCESS as a front end without bogging down on a slow network (i.e. modem at 56 Kbps) is much too limiting for most applications.
Is there no OLEDB connectivity?
The same element of remote database connectivity over the web can be accomplished for ANY ODBC-compliant database using dbBridge for Windows. More information is available at www.dbbridge.com, but in general, a user wishing to connect to a databaseremotely, and not wanting to rely on a persistant connection, can do so effectively with dbBridge. DbBridge uses the internet as a transport medium, and can employ techniques such as HTTP cloaking and encription to ensure data security.
A great database analysis tool for Access 97, 2000, and XP can be found at http://www.datamoxie.com
When you have a Binary field, the ODBC link can't resolve it into Access, so whenever you attempt to view a table with a Binary field, you end up with an ODBC call failed error [Oracle][ODBC]#S1000)0 error and all fields/records appear at #Name? It's easy to resolve by writing a query/report that doesn't involve the binary fields, but is there a way to view the binary field in Access?
PS - don't forget the 255 field/table limitation.
PS - don't forget the 255 field/table limitation.
Viewing Oracle Binary fields from Access depends on how the Binary fields were created and what kind of data is in them. If 'standard' objects are placed in the binary field (Microsoft Office files, various types of images, etc.; i.e., those that Access 'knows'), Access can easily display the contents. I've used this to create/view Oracle databases of Microsoft documents (Word, Excel, Powerpoint, etc.) and to store/retrieve images. Unfortunately, it's also possible to create binary files with other apps that are not viewable by Access. One example is Oracle Form's default image format; I currently run an Oracle Form app to convert those images to a temporary TIFF file for use in an Access report.
It's been my experience that in order to update the MySQL database, a timestamp field had to be added to each table for Access to hook onto. This is in addition to any primary key setting.
The article makes no mention of this requirement. Have they fixed this (I'm using MS Access 2k with no desire to upgrade)
The article makes no mention of this requirement. Have they fixed this (I'm using MS Access 2k with no desire to upgrade)
As far as I know, there's no requirement for a Timestamp field.
Just to make sure, I created a new access database and selected my very remote MySQL database. I created some records and deleted some, and then exited Access. Finally, I made a local ASP/ADO file to query the remote data base. The result set confirmed that the changes were made.
Just to make sure, I created a new access database and selected my very remote MySQL database. I created some records and deleted some, and then exited Access. Finally, I made a local ASP/ADO file to query the remote data base. The result set confirmed that the changes were made.
I believe Timestamp on SQL-Server is mainly used to speed up a record modification. SQL will verify only the Timestamp value before modifying a record, instead of verifying all the fields in the record. It is mainly used to report a write conflict if the record was modified while it was being modified by the Access instance.
I do not know if MySQL is using the Timestamp in the same way. But it does improve record modification with Access and SQL.
I do not know if MySQL is using the Timestamp in the same way. But it does improve record modification with Access and SQL.
What I'm seeing here is that Access 2000 connecting to a real database server like Oracle via ODBC is pulling the entire table across the net on every query no matter if it brings 1 record or 10000?
Is there another easy to use tool like Access that doesn't do that? Does Excel 2000 suffer the same fate as Access in terms of full table scans?
Is there another easy to use tool like Access that doesn't do that? Does Excel 2000 suffer the same fate as Access in terms of full table scans?
In reply to:
Is there another easy to use tool like Access that doesn't do that? Does Excel 2000 suffer the same fate as Access in terms of full table scans?
----- ----- ----- ----- -----
Access and Excel would both be lobbing the whole data file back and forth across the network.
They are both (as well as many others) capable of being used as a front end. However, as the size or concurrency of the database increases, their value as a F/E declines. For the real power-functions, you'rebest off with the SQL Manager or whatever Oracle uses.
I just finished an article for Builder.com on SQLYog.com. It's an EXCELLENT F/E for MySQL!
Is there another easy to use tool like Access that doesn't do that? Does Excel 2000 suffer the same fate as Access in terms of full table scans?
----- ----- ----- ----- -----
Access and Excel would both be lobbing the whole data file back and forth across the network.
They are both (as well as many others) capable of being used as a front end. However, as the size or concurrency of the database increases, their value as a F/E declines. For the real power-functions, you'rebest off with the SQL Manager or whatever Oracle uses.
I just finished an article for Builder.com on SQLYog.com. It's an EXCELLENT F/E for MySQL!
When connecting to Oracle via ODBC, Access DOES NOT pull the whole table across the network to the local machine. The SQL is sent to the server, and it simply returns the result set to the client. I have an oracle table with about 200 million records in it (more than 40GB in size), and we use Access to query this table without any problems at all.... 40GB is definitely not being sent to my PC for each query.
i am looking for a tutorial or howto on using access and sql server to create a dynamic parameter query to input the parameters in a form and to display the query results on a subform or another form using sql server.
thanks
thanks
When I link through Access to a MySQL table I get all the records. I want to add records through Access, nut this option is not available. Do I have to do it programmatically through SQL or is there something I can adjust in MySQL?
Hello everyone,
Does anyone know how to execute and have its results returned to Access 2000? Can this even be done? I would like to create a front end with Access 2000 for a user so that they can execute 3 stored procs which should return the results regarding a task. Any help would be great! Oh, yes...my database is MSSQL 7.0 / 2000
Thanks.
Please email me at dbscripts2002@yahoo.com
Does anyone know how to execute and have its results returned to Access 2000? Can this even be done? I would like to create a front end with Access 2000 for a user so that they can execute 3 stored procs which should return the results regarding a task. Any help would be great! Oh, yes...my database is MSSQL 7.0 / 2000
Thanks.
Please email me at dbscripts2002@yahoo.com
JET is going the way of win 9x. I am only a novice but the switch to MSDE for ADP's is seamless. Beside you get SQL server. The Standard Access limitations still exist but this is a small price to pay.
Carl
Carl
when i open a table in access the primary key is seen by access as just a normal number, although in mysql its fine (auto incrementing) is there any way to make this work correctly?
anyone know where i can find a tutorial or a how to, to code a parameter query using a sql server, i can not use parameters referencing forms objectsForms!F_Emp!Sort_Option on a query accesing a sql server database
I don't know how to use database, please help me how to use database!!!
dear ser i need to develope a simple database using ms access for my hotel HRM departement the employees are 250 and there are three shift for the worker so i need ur help and thanks in advance.
This is a great article. In the fast-paced IT industry I find it amazing that someone's article 5 years ago still holds.
??Great post. Here???s how you can convert Microsoft Access to web in minutes http://www.caspio.com/extend/platform-extensions/ms-access-database-online/convert-ms-access-to-web.aspx
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
