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.SetWarnings 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.SetWarnings 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.com
http://www.accessmonster.com/Uwe/For...ccess/200805/1 
