Solved

Linked tables showing "#Deleted"

Posted on 2004-09-05
9
212 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 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