Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Linked tables showing "#Deleted"

Posted on 2004-09-05
9
Medium Priority
?
215 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:PstWood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
9 Comments
 

Expert Comment

by:xbww
ID: 11986839

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
 

Author Comment

by:PstWood
ID: 11986860
I'm only using Access 97, so how much of what you posted applies?

Thanks
RWW
0
 
LVL 34

Expert Comment

by:flavo
ID: 11986894
I have Office 97 and installed MDAC 2.6
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Expert Comment

by:xbww
ID: 11986919
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
 
LVL 34

Expert Comment

by:flavo
ID: 11986930
database may also be corupt.  create a new access container (new mdb) and try again...
0
 

Author Comment

by:PstWood
ID: 11989475
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 12010222
> 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
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 12029980
PAQd, 100 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question