MySQL Forums - Microsoft Access

Dropping a field in view (no replies)

Rolex Man — Sat, 27 Apr 2013 13:25:36 +0000

Hi, I'm a newbie in MySQL. I live in a MS Access world most of the time.

I want to link a MySQL table remotely using the ODBC connector. I've learned that it's not "secured", but I've no idea what that means. Does it mean the data can be intercepted through cyberspace during transmission?

If it is unsecured, then I would like to only grab the non-critical information from the tables. I don't think it is possible to link a save query (I can be wrong), but I've learned that you can duplicate and link a virtual table which turns to VIEW. Is there a way to drop a column in view? I tried the same SQL to drop a column as you would for a table, but it's a no-go. Can anyone help? If it's not possible to drop a column in VIEW, can anyone suggest how I can accomplish what I'm doing, which is to link a table with certain columns only?

normalization & calculated fields (no replies)

Daniel Hofer — Thu, 25 Apr 2013 20:27:33 +0000

I ported my large end user developed Paradox database of 18 years to an Access\MySQL database this past year. In preparation for retirement next year I'm now working with IT to normalize part of the database to remove calculated fields as they work on porting some of the Access forms and reports to php web forms.

Relevent Part of Model:
`orders` 1 to many `jobs` 1 to many `lineitems` 1 to 1 `biditems`.

Calculated Fields to remove:
`lineitems`.`extended total` = `lineitems`.`units` * `biditems`.`price`.
`jobs`.`jobvalue` = the sum of related `lineitems`.`extended total` records
`orders`.`ordervalue` = the sum of related `jobs`.`jobvalue` records.

Everything is (re)calculated with a stored procedure when lineitems are added or updated.

If I remove the calculated fields in `lineitems`, `jobs` and `orders` I know how to use queries to calculated these values on the fly for reports.

What do I do to show these calculated control values in the form? The Orders form has a subform showing the jobs rows for an order and the jobs subform has a subsubform showing the lineitems rows for each job. I can handle the lineitems subform but not the jobs subform `jobvalue or orders form `ordervalue` fields. I can't base the forms on aggregation queries because of the need to update fields.

For the `jobs` subform `jobvalue` field I tried a row source expression:

=Sum(
IIf([jobno]=[Forms]![frmJobs]![frmLineitems].[Form]![jobno],
[Forms]![frmJobs]![frmLineitems].[Form]![units]
*
[Forms]![frmJobs]![frmLineitems].[Form]![price],
0)
)

This gives an error. Even if it works summing the above expression in an equivalent expression to calculate the ordervalue as sum of jobvalues seems very messy. It's even more complicated since there is an second parallel field earnedvalue at each level which is value * percentcomplete.

I'm open to ideas.

Excel formulas (no replies)

Malcolm Debono — Thu, 18 Apr 2013 09:54:29 +0000

Hi
Sorry wrong forum

ODBC 5.2 Connectors (infinite parameters) (no replies)

Mike Yin — Thu, 11 Apr 2013 09:19:02 +0000

Hi members

Just starting to connect from Access.02 Front End to remote MySQL using ODBC above. In the configuration dialog of this, there are so many parameters in the "Details" and am not sure what must be use & what not.

To avoid disconnection which will occur after a short time, i tick the "Enable automatic reconnect" & "Dont prompt on connecting". With this everytime a form is opened, it just do it without asking or tell you that there is no connection yet. However after leaving the form for some time, it still report the the connection is lost. How to fix this?

Essentially i would appreciate a specific link to some basic write up on the use of this infinitely interesting & width DB in the vast ocean. Smile.

Can't add records from ACCESS some tables (no replies)

Jim Attrill — Tue, 02 Apr 2013 12:07:25 +0000

I exported a few tables from an Access Db to MySql. Now I notice that some of the forms allow new records >* and on the most important one (of course) the add record asterisk is 'greyed out'. Why is this?

Access to MySQL, ASP website (no replies)

Mac Browne — Mon, 25 Mar 2013 00:00:55 +0000

Hi all

Completely new to MySQL and need some advice.

Currently I'm using MS Access on my ASP website for the shop.
I'm thinking of changing to MySQL (I have the facilities to due so with my host), my one concern is the web pages, will I have a massive job on my hands changing code or is it pretty much compatible?

How to repair corrupted MS access data (2 replies)

Undelete Files — Fri, 26 Apr 2013 06:59:29 +0000

Have you lost your saved data from existing MS access files than what steps have have to follow please share your opinion
http://www.unistal.com/access-recovery.html

Importing backup from Access 2003 into Access 2007 (no replies)

Murray Muspratt-Rouse — Mon, 18 Mar 2013 09:09:35 +0000

When I restore a backup taken from MySQL Community Server 5.5.30 under Windows XP from tables used by an Access 2003 database (.mdb) onto the same server version (32 bit) running under Windows 7 64 bit used by an Access 2007 version of the same database (.accdb) multiple record locks are reported when running a function that attempts to update multiple records.

Is there some way of avoiding this problem by changing the setup for the backup method? Currently I use the backup from MySQL Administrator 1.2.17.

Can I unlock the records/tables on the Windows 7 system?

All help gratefully received!

Murray

MS Access ODBC connector Flags (no replies)

Luke Bourke — Wed, 13 Mar 2013 22:55:20 +0000

So this question has been bugging me and affecting our data analyses for some time now. Now that we have large tables and getting larger every month since a monthly import just keeps building on existing data to make them larger and larger (the largest right now is 1GB!), we need to know what some of the suspect parameters mean and figure out a way to optimize the connection between the MS Access frontend and the MySQL Server backend.

I had previously looked at http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-usagenotes-apptips.html

The section that begins with "If you have very large (long) tables in Access, it might" is the paragraph that seems to make a relevant point.

My problem is:

1. Whenever I try to select different flag options, and then close the Access db, and reopen, the dsn will prompt again asking for credentials and I can see that the flags go back to the way they were to begin with (in my case, the only flag set is Allow Big Results). I guess this would be overcome by opening the dsn through Control Panel and selecting the flags I want to set (hasn't reprompted me upon opening the database so can't see if it is going back to the settings it had, like as if it is 'hard-coded' in the DSN -- see #2??)

2. Not too clear about what the Return Matching Rows (2) and Allow BIG Results (8) flags do to help open the large (long) tables (I mean, I looked them up in the MySQL Reference Manual, but need to hear an expert's language rather than how they describe them). I selected those by opening the DSN through Control Panel, and then opened the Access DB, and tried to open the largest table, but then came up with an error "ODBC--connection to 'MySQL ODBC 5.1 Driver [IP address] failed' ??

If anyone can let me know how to easily resolve this issue, I would be greatly appreciative and thankful.

Converting Access 2003 to Access 2007 (no replies)

Murray Muspratt-Rouse — Mon, 04 Feb 2013 08:27:13 +0000

8 years ago I designed, programmed and installed the first of a number of Access 2003 databases under Windows XP, using MySQL Community Server to hold the 'master' tables. I am now attempting to install those databases under Windows 7 with Access 2007. I have restored a backup from the Windows XP system, which is running Community Server 5.1.1, to create a new catalog on Community Server 5.5.10 running under Windows 7. I have tried using the Access Linked Table Manager to change the DSNs to one pointing to the catalog on the Win7 system, but Access finds an error and restarts (a total of 4 links have been updated). I have also tried removing all the links from the Access database and then importing them. My final attempt was to export a table from the Access database and then import a link to it. This failed as well.

Is there anything I can do to convert the tables held on the the server on the WinXP system or on the Win7 system to cure the problem? All suggestions gratefully received!

Problem solved! The solution was to add a 64 bit connector, even though the server is installed as 32 bit.

What are new features in latest version of Microsoft Access (no replies)

STANLEY FERNANDEZ — Tue, 15 Jan 2013 06:19:50 +0000

I need some information about new features in latest version of Microsoft Access. I am using Ms Access 2000 but not installed the newset one, Now i am interested and want to know more about it.

Slow OBDC Connection (no replies)

Marty Owen — Fri, 28 Dec 2012 17:01:47 +0000

I've been using Access 2007(64b) as a front-end to my organization's internet MySQL database to create stylized custom reports. Initially the website used MySQL version 3.5.1 and my ODBC connector used a 3.5.1 driver. Ad hoc query response time was good and response time for a MySQL view was very good. The website was just upgraded to MySQL 5.5.29 and as a result Access query response time has gone from a few seconds to several minutes, even on the MySQL views.

I set up a new connector using the latest driver (5.2w), however response time is still slow.

Using CPanel, query response time is very good, roughly the same as with 3.5.1. I've double checked my internet connection speed and it remains at the normal 18meg/sec download.

Any thoughts and suggestions would be greatly appreciated.

Marty

Why is there no tool for migrating MS-access to mysql (4 replies)

c n — Mon, 04 Feb 2013 14:29:04 +0000

Hello,

even after a long search on mysql and google I could not find any definitive answer.

So: Is there or is there not a tool for migrating MS-access to mysql that can be found on the mysql-website ?

- Mysql-workbench will NOT do this.

- The mysql-whitepaper "A Guide for Migrating From Microsoft Access to MySQL" specifically fails to mention any solution that is still working. (But at least now I am registered)

- Any tools that previously worked are marked "end of lifetime"

I am aware that enough googling will find third party-tools, mostly commercial. Please do not reply with any third-party links (thx)

So does mysql/oracle have anything for this or has this subject somehow been abandoned?

THX
stn

ODBC Call Failed error When using Parameter (no replies)

Craig Froehlich — Wed, 14 Nov 2012 13:59:57 +0000

I am trying to setup an MS Access frontend to a MySQL database for reporting and query purposes. One of the reports, accepts a few parameters and then runs a query with those parameters to return results. I always receive and ODBC Call Failed error. If I manually write the query with the value in the parameter it works fine. I've also been able to determine that this will work if the field I am comparing is integer, but not varchar. So it seems to me that the query being sent is malformed, but I'm not able to determine how. Suggestions?

MS Access 2010, Windows 7, 64 bit - ODBC error 3146 (ODBC--Call Failed) after 8192 SQL Update requests (no replies)

Chuck Clark — Tue, 06 Nov 2012 13:30:25 +0000

I am consistently receiving an error:
3146 (ODBC--Call Failed) ...
after 8,192 successful SQL update requests from an MS Access 2010 front-end VBA procedure to a target table on the MySQL backend. I have tried this with three different target tables and consistently receive the same result.

I am using MS Access 2010 on a Windows 7, 64 bit Intel i7 machine (new computer, new installation) with MySQL as a backend. MS Access and MySQL are both running on the same computer, as this is a development environment. MySQL and the ODBC connector are both the latest version. I have three very large tables (18 M rows, 3 M rows, and 1 M rows) loaded in MySQL that I am trying to update using an MS Access Visual Basic for Applications (VBA) procedure.

My Access VBA procedure iterates through the target table collecting data (adOpenForwardOnly and adOpenReadOnly) and then issues an "UPDATE SET = WHERE id= " SQL request. This works correctly until the procedure reaches the 8,193rd record, when I get the error.

I have tried this with each target table several times and consistently see this failure. Because it consistently fails after the 8192nd attemps, I am thinking this may a configuration problem, but I cannot figure out how to fix it.

Can anyone tell me if this is a configuration error or a bug? Any assistance is greatly appreciated.

Chuck

Windows 7 firewall stops MySQLv5.1.65 noinstall (no replies)

Yshua Calkins — Wed, 10 Oct 2012 18:21:26 +0000

Dear Forum People:

Windows 7 refuses to allow mysql.exe as an allowable program for passing through Windows firewall. Apparently because MySQL v5.1.65 is a "noinstall," this disqualifies it. What is an acceptable workaround for Windows 7 to allow me to use this version, as my programs are incompatible with later MySQL versions!

Help!
Yshua

ODBC 4-byte UTF8 error and MS Access 2010 (8 replies)

Nert Skull — Tue, 06 Nov 2012 02:11:09 +0000

I have Access 2010. I installed the ODBC driver (v5.2) and I am running MySQL on Ubuntu 11.10 with MySQL version 5.1.63.

I am able to connect to the database, and get the tables linked. I can input numeric data (integer).

But I can NOT input text data in fields. I get the following error

ODBC--insert on a linked table 'tBought' failed.
[MySQL][ODBC 5.2(w) Driver][mysqld-5.1.63-0ubuntu0.11.10.1]Server does not support 4-byte encoded UTF8 characters. (#0)

I have looked all over, and this is the best I can come up with. I may be totally wrong on some of this, but this is what I think I've discovered thus far.

Access 2010 uses 4-byte encoded UTF8, whereas MySQL 5.1 doesn't support that (3-byte only???). So changing the collation has done nothing for me.

I then installed MySQL 5.5 (an a different computer) and it works just fine. I can link the tables and everything gets entered.

So, is there a way to make the ODBC driver work with Access 2010 and MySQL 5.1? I have other databases that in my original 5.1 install, and I would like to avoid upgrading. Especially since 5.5 is considered "testing" in the repositories still.

I'm hoping there is a way to work around this problem and make the ODBC driver work. Any ideas?

Experiencing slow run times when Access linked via ODBC to MySQL table (no replies)

H L — Tue, 11 Sep 2012 20:36:02 +0000

Our company has an Access database that we propose to move to a more robust platform. The initial investigation has involved running the Access code against tables linked ODBC to external databases.

Our first timings were running Access code against the internal Access tables. With the .MDB file on the network drive, our Task took 8min37sec. We moved the .MDB to the local hard drive, and the run time went down to 2min17sec.

Our second step was to move the data table (189,000 records and 94 fields) into a database on Microsoft SQL Server. We linked from Access to the table on SQL Server, and got run times of 2min08sec.

Our third step was to move the same table into a MySQL table. We linked from Access to the table in the MySQL database on the network drive, and ran out of patience before the run completed. Step 3B was to move that MySQL database to the local C: drive, and link to that. Our run time was 32min27sec.

Summed up; Moving Access .MDB to local drive made things faster, linking to MS SQL Server on network made things faster, but even MySQL on the local hard drive is really much slower.

I hope that I have done something wrong, perhaps I have some setting configured incorrectly. I have all the latest files, since I just downloaded them, and I accepted the default options during the installs.

Any advice that you can give to a newbie like me will be greatly appreciated.

MS Access and MySQL Query Browser (no replies)

Harry Branson — Thu, 06 Sep 2012 21:08:09 +0000

A SELECT all query in MS Access returns all rows in a table. A SELECT all query in the MySQL Query Browser on the same table returns only roughly two-thirds of the rows. A SELECT WHERE query on the same table in the MySQL Query Browser doesn't return a row if the WHERE value is past the roughly two-thirds of the rows. Is there something in the setup or an INI file I haven't set correctly? Thanks.

Microsoft Access 2010 - Linking Tables (no replies)

Joe Chambers — Mon, 27 Aug 2012 22:40:44 +0000

I am having problems getting all my MySQL 5.0.92 tables to link up with my MS Access 2010 interface. Some of the tables will work fine, but others will crash Access when I try to link them. They all show up in my list when I select the database and a test of the database connection works fine when setting up the ODBC connection. I have the option for allowing big results sets checked and the debug log turned on, but debug returns no messages other than showing the connection.

Write Conflict Using Access as Front End (no replies)

JJ Campbell — Sun, 19 Aug 2012 17:05:25 +0000

I have an Access 2007 connecting to my MySQL table via ODBC. I've read everything I can find on this problem. I've got a TimeStamp column, no booleans and only one numeric and it defaults to zero. I was having trouble getting the dates in the right format so I exported the data from Excel as a tab delimited text file after changing all the dates to English(UK) YYYY-MM-DD.
The TimeStamp column was created via an SQL statement (alter table members change `tsChanged` `tsChanged` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) since EarthLinks 2.11 phpMyAdmin would not let me do it directly.

Everything is fine for any new records I create in either Access or via phpMyAdmin directly on the server. However, any of the records I imported cannot be edited via Access - I get the dreaded Write Conflict message. I tried an update query on the MySQL server, and updated the TimeStamp to NOW() but that did not fix the problem - I could still edit the records I created after the import but not the imported ones.

Also, I checked the ODBC connector option for "Return matched rows instead of affected rows."

I am deperate. I've tried everything I can think of and every suggestion that I've found to no avail.

Please help if you can!
J. J. Campbell, Arlington, VA

Database plan? (no replies)

steve campbell — Tue, 07 Aug 2012 14:37:34 +0000

Can I get some advice, are these things possible?

Database plan:
1. Design database in access then split to front end access and back end to mysql.
2. Setup users and passwords
3. Setup users level rights (users can only change their data) (grant users rights to view other data or not)
4. Setup administration to view all data.

Read MS Access table from MySql StoredProcedure (no replies)

David Blochstein — Fri, 03 Aug 2012 20:26:04 +0000

Really need to select from MS Access table in MySql stored procedure.
Any thoughts?
Thank you

How to connect by using ODBC....... (no replies)

Bizworldusa gcm — Fri, 03 Aug 2012 15:45:00 +0000

How to connect to MsExcel, MsAccess by using ODBC ? Please help me.........

Thank you
Bizworldusa

Just Connecting my database (no replies)

salman baig — Tue, 31 Jul 2012 09:29:58 +0000

Hello fellows,

i'm just facing a problem while connecting database, its gives me error tell me please what to do with this problem ? http://www.rockpaki.com/

Help me to stop using MS Access (no replies)

Craig Orput — Tue, 24 Jul 2012 02:01:34 +0000

I've been using MS Access for years but about 4 years ago I switched over to Ubuntu and open source applications. I want to get away from MS Access but don't know where to start. I'm not sure if its because I have use MS Access for over a decade or it truly is a decent UI. In access, you design your tables, queries and then go into a nice for builder to create the front end. Nice drag and drop stuff, menuing systems, the works.

I've been looking for a similar front end to MySQL that is open source but haven't found it. I don't know if that's because I'm not searching for the right thing or if they just don't exist. What in the world do you folks use to build your GUI on top of the database?

Thanks

change/translate access function switch() in to CASE clause for pass-through query (no replies)

Nejc Draganjec — Fri, 06 Jul 2012 18:12:58 +0000

Hy guys. I'm lost here. I had this multiple query cascade but then I changed my back end from native access to MySQL. And cascade of queries suddenly became way too slow. So I sat down and I'm trying to "translate/rewrite" them in to on big pass-through query with multiple sub-queries. Actually I had a great success and everything before this last step runs without problem and EXTREMELY fast :)! Hurray for MySQL!

But now I'm stuck in one LAST FINAL STEP :)!

What I got is:
SQLString = "SELECT T1.DATUM, T1.ID_SeznamURE, T1.ZasedeniTermini AS ZasedeniTermini, " & _
"(SELECT Avg(ZasedeniTermini) " & _
"FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS T " & _
"WHERE T.DATUM = T1.DATUM AND T.ID_SeznamURE BETWEEN switch(T1.ID_SeznamURE - T2.mini <= 2, T2.mini, T2.maxi - T1.ID_SeznamURE <= 2, T2.maxi - 4, true, T1.ID_SeznamURE - 2) AND switch(T2.maxi - T1.ID_SeznamURE <= 2, T2.maxi, T1.ID_SeznamURE - T2.mini <= 2, T2.mini + 4, true, T1.ID_SeznamURE + 2) " & _
") AS UrnoPovprecje " & _
"FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS T1 INNER JOIN (SELECT D.DATUM, MIN(D.ID_SeznamURE) AS mini, MAX(D.ID_SeznamURE) AS maxi FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS D GROUP BY D.DATUM) AS T2 ON T1.DATUM = T2.DATUM " & _
"ORDER BY T1.DATUM, T1.ID_SeznamURE;"

But since switch() is function that MySQL does not recognize I got an error.

So I searched internet and as I understand I should use CASE clause for same results. So I came up with that:

SQLString = "SELECT T1.DATUM, T1.ID_SeznamURE, T1.ZasedeniTermini AS ZasedeniTermini, " & _
"(SELECT Avg(ZasedeniTermini) " & _
"FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS T " & _
"WHERE T.DATUM = T1.DATUM AND T.ID_SeznamURE BETWEEN (CASE WHEN T1.ID_SeznamURE - T2.mini <= 2 THEN T2.mini " & _
"WHEN T2.maxi - T1.ID_SeznamURE <= 2 THEN T2.maxi - 4 " & _
"WHEN true THEN T1.ID_SeznamURE - 2 " & _
"ELSE BEGIN END) " & _
"END CASE) " & _
"AND (CASE WHEN T2.maxi - T1.ID_SeznamURE <= 2 THEN T2.maxi " & _
"WHEN T1.ID_SeznamURE - T2.mini <= 2 THEN T2.mini + 4 " & _
"WHEN true THEN T1.ID_SeznamURE + 2 " & _
"ELSE BEGIN END) " & _
"END CASE) " & _
") AS UrnoPovprecje " & _
"FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS T1 INNER JOIN (SELECT D.DATUM AS DATUM, MIN(D.ID_SeznamURE) AS mini, MAX(D.ID_SeznamURE) AS maxi FROM " & sql_QryURNIK_ZASEDENI_TERMINI_SESTEVEK & " AS D GROUP BY D.DATUM) AS T2 ON T1.DATUM = T2.DATUM " & _
"ORDER BY T1.DATUM, T1.ID_SeznamURE;"

And now I'm really lost since I can't see any mistake and I still get an error. This time it says:
You have an error in your SQL syntax.; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS UrnoPovprecje FROM (SELECT c.DATUM, c.ID_SeznamURE, Count(c.ID_SeznamURE) A' at line 1

But I think problem is still in syntax or use of CASE clause. But hey, I could be wrong :)! I have no idea what could be going on here, so if any of you MySQL jedis could lend me some of your force and show me the path, I would be really grateful!

THANK YOU!

Bonsai collection on MS Access (no replies)

Stephen Rynas — Mon, 11 Jun 2012 12:44:34 +0000

Felix Laughlin asked in December 19, 2012 about using MYSQL for a Bonsai collection. This post is now "locked" so I cannot respond to it directly.
forums.mysql.com/read.php?65,506243,506243

Felix wrote: "I have been using MS Access for many years to accumulate photos and text on my bonsai collection. It has worked nicely because I have an entry for each bonsai and there I have (1) notes on what I have done in the care and improvement of the tree and (2)successive photos of the tree taken over time. However, I am having problems with the database which has gotten large and unwieldy, and am looking for a better way going forward. An IT expert suggested Mysql which I am unfamiliar with."

My intitial reaction, if you (Felix) are still checking the forum, is that you should not be having this type of issue with Access. The issue may be in how you are "storing" the photos. Photos are not stored in the database itself, but are referred too.

tables not seen when using odbc to connect access as front end to mysql backend (2 replies)

S Narayanan — Thu, 06 Sep 2012 05:35:55 +0000

I am working on mysql for the first time ever, and have sometimes used access 2007.

I have created an Access 2007 database, which is basically for inputting enquiries from clients, and quotations for the same from suppliers.

I want to convert my back end to mysql, since this way i can have people in diff cities inputing their enqiuiries and quotes. I would like to however retain Access 2007 frontend, so that i can run my access queries and reports without having to redo the whole thing.

I have been able to convert the backend into mysql, and have been able to upload it to my website database. I now need to have access forms on the individual computers of my colleagues in diff cities, for them to be able to input the data.

I tried to link the mysql databse saved on my computer (as a trial), to the front end on my computer. The Mysql ODBC 5.1 Driver is installed, and I selected the datasource as the file name. In the database field, i get only 2 options i.e. test and information_schema.

when i select test, the connection is confirmed.

Then when i try to get external data (ODBC files).....i am able to see the file on the machine data source....but when i click it, there are no tables seen.

what am I doing wrong? Is there an easier way to connect my access front end to the mysql database on my website?

Pl help

How To Link Access file from SQL ? (no replies)

John Arnold — Mon, 28 May 2012 12:54:42 +0000

Can you tell me how to link access file from SQL helpl me send me rpl.
Designer Leather Sandals | Leather Sandals UK