D
Danny J. Lesandrini
Iain:
I'm getting an error when I try to compact, object MSysCompactError not found.
I did a newsgroup search and found this old post from Michka. Basically, your
mdb is corrupt.
http://groups.google.com/group/micr...a525d069c3a?q=MsysCompactError+group:*Access*
That having been said, I was able to pull tblArea-Team out of that database into
a blank database with the following SQL:
SELECT * INTO [tblArea-Team]
FROM [C:\Documents and Settings\Administrator\Desktop\TrainingDatabase.mdb].[tblArea-Team]
WHERE [tblArea-Team].OfficeID >0
Notice two things: First, there is a zero record. While this isn't by itself a problem,
(I have been known to add zero records to tables with AutoNumbers) in this case it
probably confirms the corruption.
If it were me, I might try to delete all the zero rows. Actually, I did try and got
a corruption error, so that might not work.
The second thing to notice is that my SQL above uses the file path and the SELECT INTO
to create a sql statement in a non-corrupted empty database that works. I excluded the
zero record and threw the rest into a new table. It worked, but all the field sizes were lost.
The table [tblArea-Team] was created and has 17 rows.
So I next created a function to interrogate the recordset to see what I could find out
about the table, and see below what I got ... field sizes. One could write a function, and
maybe somebody already has, to create a table based on the metadata in the recordset.
Then this code could loop through and add records.
Or, if you happen to have an old, non-corrupt version around, that could be used as the
repository for the data. It will take work, but could be done.
If you have a complete list of tables, the above SQL could be used to pull out all the data
and then work with that. I guess the point is, you do have data, and you do have options,
but that mdb won't probably ever show it's tables again.
Danny
Function fooLookAtTable()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strsql As String
Dim fShowFieldMetadata As Boolean
strsql = "select * FROM [C:\Documents and Settings\Administrator\Desktop\TrainingDatabase.mdb].[tblArea-Team]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
fShowFieldMetadata = True
Do Until rst.EOF
If fShowFieldMetadata = True Then
For Each fld In rst.Fields
Debug.Print fld.Name & " " & fld.Type & " " & fld.Size
Next
fShowFieldMetadata = False
End If
For Each fld In rst.Fields
Debug.Print fld
Next
rst.MoveNext
Loop
Set rst = Nothing
Set dbs = Nothing
End Function
OfficeID 4 4
AreaName 10 50
Address1 10 50
Address2 10 50
Address3 10 50
Address4 10 50
Postcode 10 8
PhoneNo_(STD) 10 10
PhoneNo_ 10 12
FaxNo-(STD) 10 10
FaxNo_ 10 12
0
Null
Null
Null
Null
Null
Null
Null
Null
Null
Null
1
Youth Service (Headquarters)
1 City Road
Null
Bradford
Null
BD8 8ER
01274
432440
01274
432502
I'm getting an error when I try to compact, object MSysCompactError not found.
I did a newsgroup search and found this old post from Michka. Basically, your
mdb is corrupt.
http://groups.google.com/group/micr...a525d069c3a?q=MsysCompactError+group:*Access*
That having been said, I was able to pull tblArea-Team out of that database into
a blank database with the following SQL:
SELECT * INTO [tblArea-Team]
FROM [C:\Documents and Settings\Administrator\Desktop\TrainingDatabase.mdb].[tblArea-Team]
WHERE [tblArea-Team].OfficeID >0
Notice two things: First, there is a zero record. While this isn't by itself a problem,
(I have been known to add zero records to tables with AutoNumbers) in this case it
probably confirms the corruption.
If it were me, I might try to delete all the zero rows. Actually, I did try and got
a corruption error, so that might not work.
The second thing to notice is that my SQL above uses the file path and the SELECT INTO
to create a sql statement in a non-corrupted empty database that works. I excluded the
zero record and threw the rest into a new table. It worked, but all the field sizes were lost.
The table [tblArea-Team] was created and has 17 rows.
So I next created a function to interrogate the recordset to see what I could find out
about the table, and see below what I got ... field sizes. One could write a function, and
maybe somebody already has, to create a table based on the metadata in the recordset.
Then this code could loop through and add records.
Or, if you happen to have an old, non-corrupt version around, that could be used as the
repository for the data. It will take work, but could be done.
If you have a complete list of tables, the above SQL could be used to pull out all the data
and then work with that. I guess the point is, you do have data, and you do have options,
but that mdb won't probably ever show it's tables again.
Danny
Function fooLookAtTable()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strsql As String
Dim fShowFieldMetadata As Boolean
strsql = "select * FROM [C:\Documents and Settings\Administrator\Desktop\TrainingDatabase.mdb].[tblArea-Team]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
fShowFieldMetadata = True
Do Until rst.EOF
If fShowFieldMetadata = True Then
For Each fld In rst.Fields
Debug.Print fld.Name & " " & fld.Type & " " & fld.Size
Next
fShowFieldMetadata = False
End If
For Each fld In rst.Fields
Debug.Print fld
Next
rst.MoveNext
Loop
Set rst = Nothing
Set dbs = Nothing
End Function
OfficeID 4 4
AreaName 10 50
Address1 10 50
Address2 10 50
Address3 10 50
Address4 10 50
Postcode 10 8
PhoneNo_(STD) 10 10
PhoneNo_ 10 12
FaxNo-(STD) 10 10
FaxNo_ 10 12
0
Null
Null
Null
Null
Null
Null
Null
Null
Null
Null
1
Youth Service (Headquarters)
1 City Road
Null
Bradford
Null
BD8 8ER
01274
432440
01274
432502