Linked tables showing "#Deleted"

I'm trying to set up a database that uses MySql table on the Internet, and went about it this way:

1. Built db in Access
2. Uploaded tables to MySql db
3. Deleted origninal tables from Access
4. Linked MySql tables
5. Created original relationships in Access

The tables exist on the Mysql server, appear to have the right structure and contain data, however when I try to view that data in Access, all columns and rows contain "#Deleted". Can anyone point me in the right direction to figure out where the problem is?

Thanks.
RWW
PstWoodAsked:
Who is Participating?
 
GhostModConnect With a Mentor Commented:
PAQd, 100 points refunded.

GhostMod
Community Support Moderator
0
 
xbwwCommented:

If you are using Access 2000, you should get and install the newest (version 2.6 or above) Microsoft MDAC (Microsoft Data Access Components) from here. This will fix the following bug in Access: when you export data to MySQL, the table and column names aren't specified. Another way to around this bug is to upgrade to Connector/ODBC Version 2.50.33 and MySQL Version 3.23.x, which together provide a workaround for this bug! You should also get and apply the Microsoft Jet 4.0 Service Pack 5 (SP5), which can be found here. This will fix some cases where columns are marked as #DELETED# in Access. Note that if you are using MySQL Version 3.22, you must to apply the MDAC patch and use Connector/ODBC 2.50.32 or 2.50.34 and above to go around this problem.
For all Access versions, you should enable the Connector/ODBC option flag Return matching rows. For Access 2.0, you should additionally enable Simulate ODBC 1.0.
You should have a timestamp in all tables you want to be able to update. For maximum portability TIMESTAMP(14) or simple TIMESTAMP is recommended instead of other TIMESTAMP(X) variations.
You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.
Only use DOUBLE float fields. Access fails when comparing with single floats. The symptom usually is that new or updated rows may show up as #DELETED# or that you can't find or update rows.
If you are linking a table through Connector/ODBC, which has BIGINT as one of the column, then the results will be displayed as #DELETED#. The work around solution is:
Have one more dummy column with TIMESTAMP as the data type, preferably TIMESTAMP(14).
Check the 'Change BIGINT columns to INT' in connection options dialog in ODBC DSN Administrator
Delete the table link from access and re-create it.
It still displays the previous records as #DELETED#, but newly added or updated records will be displayed properly.
0
 
PstWoodAuthor Commented:
I'm only using Access 97, so how much of what you posted applies?

Thanks
RWW
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
flavoCommented:
I have Office 97 and installed MDAC 2.6
0
 
xbwwCommented:
Just try these...

You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.
Only use DOUBLE float fields. Access fails when comparing with single floats. The symptom usually is that new or updated rows may show up as #DELETED# or that you can't find or update rows.
If you are linking a table through Connector/ODBC, which has BIGINT as one of the column, then the results will be displayed as #DELETED#. The work around solution is:
Have one more dummy column with TIMESTAMP as the data type, preferably TIMESTAMP(14).
0
 
flavoCommented:
database may also be corupt.  create a new access container (new mdb) and try again...
0
 
PstWoodAuthor Commented:
It turned out that it was something to do with the utility that I was using to convert the tables from Access to MySQL. Intelligent converters http://www.convert-in.com/acc2sql.htm is what I ended up trying after a couple others gave problems.

End of problems.
Thanks.
0
 
Gustav BrockCIOCommented:
> For all Access versions, you should enable the Connector/ODBC option flag Return matching rows.
> For Access 2.0, you should additionally enable Simulate ODBC 1.0.

Access 2.0?
Does MySQL work with 16 bit Access 2.0?

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.