Solved

Linked tables showing "#Deleted"

Posted on 2004-09-05
9
210 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 49

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 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