Solved

Linked tables showing "#Deleted"

Posted on 2004-09-05
9
213 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 50

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

695 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