Error opening tables -- queries work fine.

**Mark** · Jun 27 '08, 07:24 PM

Re: Error opening tables -- queries work fine.

I've had this problem in the past and found it to be a corrupt record in a
memo field. Luckily, there were not too many records and identified it from
the query results. I then used the primary key from the query results to
create a delete query to remove the corrupt record.

Hope this helps,

Mark

"nimajneb via AccessMonster.c om" <u42477@uwewrot e in message
news:840cbe7814 204@uwe...

Can anyone offer me any insight on the following problem?
>
I have an Access database on a company shared drive. I'm the designer and
the only user (so far). Suddenly, any time I try to open a table, Access
crashes... but if I look at that same data by *querying* the table (SELECT
*
FROM [TableName]), everything works fine.
>
Even though my data seems fine *for now*, it's a little scary for me.
Insight? Possible solutions?
>
--
Message posted via http://www.accessmonster.com
>

**nimajneb via AccessMonster.com** · Jun 27 '08, 07:24 PM

Re: Error opening tables -- queries work fine.

Mark,

Thank you very much for your response.

The problem seems to happen when I open *any* of my tables. I've also found
that Access crashes on me when I try changing the design of a saved query [in
the problematic database]. Unfortunately, this is a database of nearly a
gigabyte, and has millions of records in some of the tables. Mind if I ask a
few more questions...

1. Did you happen to know what records you should find in your query? I'm
trying to figure out what to compare it against.
2. Do you think it would help for me to simply create a new database and
import the tables?
3. Or, perhaps, I could create a new database, import just the structures of
the tables, and then use "linked tables" and "append queries" to populate the
new tables? (Then I could populate those tables overnight using a macro, and
I'd keep my indexes. And using queries might exclude any corrupt records.)

Grateful for any suggestions,
Benjamin / nimajneB

Mark wrote:

>I've had this problem in the past and found it to be a corrupt record in a
>memo field. Luckily, there were not too many records and identified it from
>the query results. I then used the primary key from the query results to
>create a delete query to remove the corrupt record.
>
>Hope this helps,
>
>Mark
>

>Can anyone offer me any insight on the following problem?
>>

>[quoted text clipped - 6 lines]

>Even though my data seems fine *for now*, it's a little scary for me.
>Insight? Possible solutions?

--
Message posted via http://www.accessmonster.com

**nimajneb via AccessMonster.com** · Jun 27 '08, 07:24 PM

Re: Error opening tables -- queries work fine.

Problem solved!

It wasn't my database at all, but a database to which I had linked tables. I
contacted that database's administrator, changed my links for a day or two,
and changed them back once the other database was back up.

For posterity, here's how I isolated the problem:

1. I started a new database.
2. I imported the structures of all my tables -- but not the data -- keeping
all the original names.
3. I created linked tables to all my tables. It automatically gave each
table the same name but with a 1 on the end.
4. I created a table called "Importing" to check which tables would come
through successfully. It had two fields: Table, and Status.
5. I ran the following code, which should work for any database thus
structured:

Sub Import()

Dim db As Database
Dim tbl As TableDef
Dim sql As String
Dim sq2 As String

DoCmd.SetWarnin gs False

Set db = CurrentDb

For Each tbl In db.TableDefs
If tbl.Connect = "" And tbl.Attributes <-2147483648# And tbl.Name <>
"Importing" Then
'Not a linked table, not a system table, and not the "Importing" table.
Let sql = _
"INSERT INTO [" & tbl.Name & "]" & vbLf & _
"SELECT *" & vbLf & _
"FROM [" & tbl.Name & "1];"
On Error GoTo ErrorHandler
DoCmd.RunSQL sql
On Error GoTo 0
'Capture the fact that the data was successfully imported.
Let sq2 = _
"INSERT INTO Importing ( [Table], Status )" & vbLf & _
"VALUES (""" & tbl.Name & """, ""Successful"") ;"
DoCmd.RunSQL sq2
ContinueHere:
End If
Next

ExitHere:
DoCmd.SetWarnin gs True
Exit Sub

ErrorHandler:
'Capture the fact that the data import failed.
Let sq2 = _
"INSERT INTO Importing ( [Table], Status )" & vbLf & _
"VALUES (""" & tbl.Name & """, ""Failed"") ;"
DoCmd.RunSQL sq2
Resume ContinueHere

End Sub

Thanks for your response!

Best regards,
Benjamin

nimajneb wrote:

>Mark,
>
>Thank you very much for your response.
>
>The problem seems to happen when I open *any* of my tables. I've also found
>that Access crashes on me when I try changing the design of a saved query [in
>the problematic database]. Unfortunately, this is a database of nearly a
>gigabyte, and has millions of records in some of the tables. Mind if I ask a
>few more questions...
>
>1. Did you happen to know what records you should find in your query? I'm
>trying to figure out what to compare it against.
>2. Do you think it would help for me to simply create a new database and
>import the tables?
>3. Or, perhaps, I could create a new database, import just the structures of
>the tables, and then use "linked tables" and "append queries" to populate the
>new tables? (Then I could populate those tables overnight using a macro, and
>I'd keep my indexes. And using queries might exclude any corrupt records.)
>
>Grateful for any suggestions,
>Benjamin / nimajneB
>

>>I've had this problem in the past and found it to be a corrupt record in a
>>memo field. Luckily, there were not too many records and identified it from

>[quoted text clipped - 10 lines]

>>Even though my data seems fine *for now*, it's a little scary for me.
>>Insight? Possible solutions?

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200805/1

Error opening tables -- queries work fine.

Error opening tables -- queries work fine.

Comment

Comment

Comment