Question about VBA code to copy tables and queries from corrupted

L

Laurie

Hello,

I am very new to VBA. I also have a corrupted database on my hands.

When trying to open the db, we get this message:
"The database is in an unexpected state. Microsoft Access can't open it.
This database has been converted from a prior version of MS Access by using
the DAO compact database method instead of the convert database command on
the tools menu (database utilities sub menu). This has left the database in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

We have not converted it from a previous format, it was created in Access
2003 and is still in Access 2003. However, our we have been having severe
problems with our network, and I suspect that this is the problem.

Nothing I have tried has worked-- I've tried to import the tables into a
brand new database, and we get the same message.

I found the posting below, but I don't understand one piece of the code.
This part, "strQry = "SELECT * INTO [" & td.Name & "] FROM [" & td.Name &
"] IN '" & dbCorrupt.Name & "'", shows in red. Am I supposed to enter the
names of my tables/database here?

Thank you for any assistance,
Laurie


http://groups.google.com/group/micr...636c/7e3f15c638abe4e7?&hl=en#7e3f15c638abe4e7

My name is Frank Miller. Thank you for using the Microsoft Access
Newsgroups.

Simply opening the database file would not corrupt it unless the file was
being opened in another application such as Word, which would definitely
corrupt the database.

When opening a database in the Access user interface, if we receive the
error...

"The database is in an unexpected state. Microsoft Access can't open it.
This database has been converted from a prior version of MS Access by using
the DAO compact database method instead of the convert database command on
the tools menu (database utilities sub menu). This has left the database in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

...we may not be able to convert the database. This message is usually due
to some corruption that has occurred in the database's project (forms,
reports, macros, modules).

To address this issue we should use standard corruption troubleshooting to
see if the problem can be corrected. However, more than likely, all
attempts to recover it will fail with the same error. In some cases, you
can recover the Jet database objects (tables and queries) by creating a new
database and using DAO code to bring the objects over. Even though you
cannot save the project items, at least the user may be able to recover
their data and queries. To use this technique, follow these steps:

1. Make a backup copy of the original database.

2. Start Microsoft Access.

3. Create a new, blank database.

4. Press ALT+F11 or on the Insert menu, click Module to launch the Visual
Basic Editor in a separate window, and will create a new module for you.

5. On the Tools menu, click References. This will display a References
dialog box.

6. Scroll down through the list, and check the box next to "Microsoft DAO
3.6 Object Library".

7. Click OK to close the References dialog box.

8. Copy and paste the following code into the new module that is open on
the screen:

Sub RecoverCorruptDB()
Dim dbCorrupt As DAO.Database
Dim dbCurrent As DAO.Database
Dim td As DAO.TableDef
Dim tdNew As DAO.TableDef
Dim fld As DAO.Field
Dim fldNew As DAO.Field
Dim ind As DAO.Index
Dim indNew As DAO.Index
Dim qd As DAO.QueryDef
Dim qdNew As DAO.QueryDef
Dim strDBPath As String
Dim strQry As String

'Replace the path below to the path of the corrupted database

strDBPath = "C:\My Documents\Appraisals.mdb"
On Error Resume Next
Set dbCurrent = CurrentDb
Set dbCorrupt = OpenDatabase(strDBPath)
For Each td In dbCorrupt.TableDefs
If Left(td.Name, 4) <> "MSys" Then
strQry = "SELECT * INTO [" & td.Name & "] FROM [" &
td.Name &
"] IN '" & dbCorrupt.Name & "'"
dbCurrent.Execute strQry, dbFailOnError
dbCurrent.TableDefs.Refresh
Set tdNew = dbCurrent.TableDefs(td.Name)

'Recreate the indexes on the table

For Each ind In td.Indexes
Set indNew = tdNew.CreateIndex(ind.Name)
For Each fld In ind.Fields
Set fldNew = indNew.CreateField(fld.Name)
indNew.Fields.Append fldNew
Next
indNew.Primary = ind.Primary
indNew.Unique = ind.Unique
indNew.IgnoreNulls = ind.IgnoreNulls
tdNew.Indexes.Append indNew
tdNew.Indexes.Refresh
Next
End If
Next

'Recreate the queries

For Each qd In dbCorrupt.QueryDefs
If Left(qd.Name, 4) <> "~sq_" Then
Set qdNew = dbCurrent.CreateQueryDef(qd.Name, qd.SQL)
End If
Next
dbCorrupt.Close
Application.RefreshDatabaseWindow


MsgBox "Procedure Complete."
End Sub

This code should import all tables and queries from the damaged database
into the current database. Note that this solution does not take secured
databases into effect, so additional code to create workspaces, etc may be
needed if the database has been secured.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Regards, Frank Miller
Microsoft Support
 
D

Douglas J. Steele

You're the victim of word-wrap: that SQL statement is supposed to be all 1
line.

Here it is again, with continuation characters to prevent word-wrap:

strQry = "SELECT * INTO [" & td.Name & "] FROM [" & _
td.Name & _
"] IN '" & dbCorrupt.Name & "'"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Laurie said:
Hello,

I am very new to VBA. I also have a corrupted database on my hands.

When trying to open the db, we get this message:
"The database is in an unexpected state. Microsoft Access can't open it.
This database has been converted from a prior version of MS Access by
using
the DAO compact database method instead of the convert database command on
the tools menu (database utilities sub menu). This has left the database
in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

We have not converted it from a previous format, it was created in Access
2003 and is still in Access 2003. However, our we have been having severe
problems with our network, and I suspect that this is the problem.

Nothing I have tried has worked-- I've tried to import the tables into a
brand new database, and we get the same message.

I found the posting below, but I don't understand one piece of the code.
This part, "strQry = "SELECT * INTO [" & td.Name & "] FROM [" & td.Name &
"] IN '" & dbCorrupt.Name & "'", shows in red. Am I supposed to enter the
names of my tables/database here?

Thank you for any assistance,
Laurie


http://groups.google.com/group/micr...636c/7e3f15c638abe4e7?&hl=en#7e3f15c638abe4e7

My name is Frank Miller. Thank you for using the Microsoft Access
Newsgroups.

Simply opening the database file would not corrupt it unless the file was
being opened in another application such as Word, which would definitely
corrupt the database.

When opening a database in the Access user interface, if we receive the
error...

"The database is in an unexpected state. Microsoft Access can't open it.
This database has been converted from a prior version of MS Access by
using
the DAO compact database method instead of the convert database command on
the tools menu (database utilities sub menu). This has left the database
in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

..we may not be able to convert the database. This message is usually due
to some corruption that has occurred in the database's project (forms,
reports, macros, modules).

To address this issue we should use standard corruption troubleshooting to
see if the problem can be corrected. However, more than likely, all
attempts to recover it will fail with the same error. In some cases, you
can recover the Jet database objects (tables and queries) by creating a
new
database and using DAO code to bring the objects over. Even though you
cannot save the project items, at least the user may be able to recover
their data and queries. To use this technique, follow these steps:

1. Make a backup copy of the original database.

2. Start Microsoft Access.

3. Create a new, blank database.

4. Press ALT+F11 or on the Insert menu, click Module to launch the Visual
Basic Editor in a separate window, and will create a new module for you.

5. On the Tools menu, click References. This will display a References
dialog box.

6. Scroll down through the list, and check the box next to "Microsoft DAO
3.6 Object Library".

7. Click OK to close the References dialog box.

8. Copy and paste the following code into the new module that is open on
the screen:

Sub RecoverCorruptDB()
Dim dbCorrupt As DAO.Database
Dim dbCurrent As DAO.Database
Dim td As DAO.TableDef
Dim tdNew As DAO.TableDef
Dim fld As DAO.Field
Dim fldNew As DAO.Field
Dim ind As DAO.Index
Dim indNew As DAO.Index
Dim qd As DAO.QueryDef
Dim qdNew As DAO.QueryDef
Dim strDBPath As String
Dim strQry As String

'Replace the path below to the path of the corrupted database

strDBPath = "C:\My Documents\Appraisals.mdb"
On Error Resume Next
Set dbCurrent = CurrentDb
Set dbCorrupt = OpenDatabase(strDBPath)
For Each td In dbCorrupt.TableDefs
If Left(td.Name, 4) <> "MSys" Then
strQry = "SELECT * INTO [" & td.Name & "] FROM [" &
td.Name &
"] IN '" & dbCorrupt.Name & "'"
dbCurrent.Execute strQry, dbFailOnError
dbCurrent.TableDefs.Refresh
Set tdNew = dbCurrent.TableDefs(td.Name)

'Recreate the indexes on the table

For Each ind In td.Indexes
Set indNew = tdNew.CreateIndex(ind.Name)
For Each fld In ind.Fields
Set fldNew = indNew.CreateField(fld.Name)
indNew.Fields.Append fldNew
Next
indNew.Primary = ind.Primary
indNew.Unique = ind.Unique
indNew.IgnoreNulls = ind.IgnoreNulls
tdNew.Indexes.Append indNew
tdNew.Indexes.Refresh
Next
End If
Next

'Recreate the queries

For Each qd In dbCorrupt.QueryDefs
If Left(qd.Name, 4) <> "~sq_" Then
Set qdNew = dbCurrent.CreateQueryDef(qd.Name, qd.SQL)
End If
Next
dbCorrupt.Close
Application.RefreshDatabaseWindow


MsgBox "Procedure Complete."
End Sub

This code should import all tables and queries from the damaged database
into the current database. Note that this solution does not take secured
databases into effect, so additional code to create workspaces, etc may be
needed if the database has been secured.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Regards, Frank Miller
Microsoft Support
Click to expand...
 
L

Laurie

Thank you so much for that.

I think I'm still missing something, however, because I get the message box
at the end that the procedure is complete, but there are no tables or queries
in my blank database. Am I missing something?



Douglas J. Steele said:
You're the victim of word-wrap: that SQL statement is supposed to be all 1
line.

Here it is again, with continuation characters to prevent word-wrap:

strQry = "SELECT * INTO [" & td.Name & "] FROM [" & _
td.Name & _
"] IN '" & dbCorrupt.Name & "'"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Laurie said:
Hello,

I am very new to VBA. I also have a corrupted database on my hands.

When trying to open the db, we get this message:
"The database is in an unexpected state. Microsoft Access can't open it.
This database has been converted from a prior version of MS Access by
using
the DAO compact database method instead of the convert database command on
the tools menu (database utilities sub menu). This has left the database
in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

We have not converted it from a previous format, it was created in Access
2003 and is still in Access 2003. However, our we have been having severe
problems with our network, and I suspect that this is the problem.

Nothing I have tried has worked-- I've tried to import the tables into a
brand new database, and we get the same message.

I found the posting below, but I don't understand one piece of the code.
This part, "strQry = "SELECT * INTO [" & td.Name & "] FROM [" & td.Name &
"] IN '" & dbCorrupt.Name & "'", shows in red. Am I supposed to enter the
names of my tables/database here?

Thank you for any assistance,
Laurie


http://groups.google.com/group/micr...636c/7e3f15c638abe4e7?&hl=en#7e3f15c638abe4e7

My name is Frank Miller. Thank you for using the Microsoft Access
Newsgroups.

Simply opening the database file would not corrupt it unless the file was
being opened in another application such as Word, which would definitely
corrupt the database.

When opening a database in the Access user interface, if we receive the
error...

"The database is in an unexpected state. Microsoft Access can't open it.
This database has been converted from a prior version of MS Access by
using
the DAO compact database method instead of the convert database command on
the tools menu (database utilities sub menu). This has left the database
in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

..we may not be able to convert the database. This message is usually due
to some corruption that has occurred in the database's project (forms,
reports, macros, modules).

To address this issue we should use standard corruption troubleshooting to
see if the problem can be corrected. However, more than likely, all
attempts to recover it will fail with the same error. In some cases, you
can recover the Jet database objects (tables and queries) by creating a
new
database and using DAO code to bring the objects over. Even though you
cannot save the project items, at least the user may be able to recover
their data and queries. To use this technique, follow these steps:

1. Make a backup copy of the original database.

2. Start Microsoft Access.

3. Create a new, blank database.

4. Press ALT+F11 or on the Insert menu, click Module to launch the Visual
Basic Editor in a separate window, and will create a new module for you.

5. On the Tools menu, click References. This will display a References
dialog box.

6. Scroll down through the list, and check the box next to "Microsoft DAO
3.6 Object Library".

7. Click OK to close the References dialog box.

8. Copy and paste the following code into the new module that is open on
the screen:

Sub RecoverCorruptDB()
Dim dbCorrupt As DAO.Database
Dim dbCurrent As DAO.Database
Dim td As DAO.TableDef
Dim tdNew As DAO.TableDef
Dim fld As DAO.Field
Dim fldNew As DAO.Field
Dim ind As DAO.Index
Dim indNew As DAO.Index
Dim qd As DAO.QueryDef
Dim qdNew As DAO.QueryDef
Dim strDBPath As String
Dim strQry As String

'Replace the path below to the path of the corrupted database

strDBPath = "C:\My Documents\Appraisals.mdb"
On Error Resume Next
Set dbCurrent = CurrentDb
Set dbCorrupt = OpenDatabase(strDBPath)
For Each td In dbCorrupt.TableDefs
If Left(td.Name, 4) <> "MSys" Then
strQry = "SELECT * INTO [" & td.Name & "] FROM [" &
td.Name &
"] IN '" & dbCorrupt.Name & "'"
dbCurrent.Execute strQry, dbFailOnError
dbCurrent.TableDefs.Refresh
Set tdNew = dbCurrent.TableDefs(td.Name)

'Recreate the indexes on the table

For Each ind In td.Indexes
Set indNew = tdNew.CreateIndex(ind.Name)
For Each fld In ind.Fields
Set fldNew = indNew.CreateField(fld.Name)
indNew.Fields.Append fldNew
Next
indNew.Primary = ind.Primary
indNew.Unique = ind.Unique
indNew.IgnoreNulls = ind.IgnoreNulls
tdNew.Indexes.Append indNew
tdNew.Indexes.Refresh
Next
End If
Next

'Recreate the queries

For Each qd In dbCorrupt.QueryDefs
If Left(qd.Name, 4) <> "~sq_" Then
Set qdNew = dbCurrent.CreateQueryDef(qd.Name, qd.SQL)
End If
Next
dbCorrupt.Close
Application.RefreshDatabaseWindow


MsgBox "Procedure Complete."
End Sub

This code should import all tables and queries from the damaged database
into the current database. Note that this solution does not take secured
databases into effect, so additional code to create workspaces, etc may be
needed if the database has been secured.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Regards, Frank Miller
Microsoft Support
Click to expand...
Click to expand...
 
G

George Nicholson

With "On Error Resume Next" at the top of the procedure, you will always get
"Procedure Complete", even if
Set dbCorrupt = OpenDatabase(strDBPath)
failed. And if that line fails, so will everything else up to "Procedure
Complete".

That line could fail for a number of reasons: from 'a password is needed' to
'the file is totally hosed'. I'd suggest commenting out the "On Error..."
line and see what your error messages tell you.

HTH & Good Luck!



Laurie said:
Thank you so much for that.

I think I'm still missing something, however, because I get the message
box
at the end that the procedure is complete, but there are no tables or
queries
in my blank database. Am I missing something?



Douglas J. Steele said:
You're the victim of word-wrap: that SQL statement is supposed to be all
1
line.

Here it is again, with continuation characters to prevent word-wrap:

strQry = "SELECT * INTO [" & td.Name & "] FROM [" & _
td.Name & _
"] IN '" & dbCorrupt.Name & "'"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Laurie said:
Hello,

I am very new to VBA. I also have a corrupted database on my hands.

When trying to open the db, we get this message:
"The database is in an unexpected state. Microsoft Access can't open
it.
This database has been converted from a prior version of MS Access by
using
the DAO compact database method instead of the convert database command
on
the tools menu (database utilities sub menu). This has left the
database
in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

We have not converted it from a previous format, it was created in
Access
2003 and is still in Access 2003. However, our we have been having
severe
problems with our network, and I suspect that this is the problem.

Nothing I have tried has worked-- I've tried to import the tables into
a
brand new database, and we get the same message.

I found the posting below, but I don't understand one piece of the
code.
This part, "strQry = "SELECT * INTO [" & td.Name & "] FROM [" & td.Name
&
"] IN '" & dbCorrupt.Name & "'", shows in red. Am I supposed to enter
the
names of my tables/database here?

Thank you for any assistance,
Laurie


http://groups.google.com/group/micr...636c/7e3f15c638abe4e7?&hl=en#7e3f15c638abe4e7

My name is Frank Miller. Thank you for using the Microsoft Access
Newsgroups.

Simply opening the database file would not corrupt it unless the file
was
being opened in another application such as Word, which would
definitely
corrupt the database.

When opening a database in the Access user interface, if we receive the
error...

"The database is in an unexpected state. Microsoft Access can't open
it.
This database has been converted from a prior version of MS Access by
using
the DAO compact database method instead of the convert database command
on
the tools menu (database utilities sub menu). This has left the
database
in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

..we may not be able to convert the database. This message is usually
due
to some corruption that has occurred in the database's project (forms,
reports, macros, modules).

To address this issue we should use standard corruption troubleshooting
to
see if the problem can be corrected. However, more than likely, all
attempts to recover it will fail with the same error. In some cases,
you
can recover the Jet database objects (tables and queries) by creating a
new
database and using DAO code to bring the objects over. Even though you
cannot save the project items, at least the user may be able to recover
their data and queries. To use this technique, follow these steps:

1. Make a backup copy of the original database.

2. Start Microsoft Access.

3. Create a new, blank database.

4. Press ALT+F11 or on the Insert menu, click Module to launch the
Visual
Basic Editor in a separate window, and will create a new module for
you.

5. On the Tools menu, click References. This will display a References
dialog box.

6. Scroll down through the list, and check the box next to "Microsoft
DAO
3.6 Object Library".

7. Click OK to close the References dialog box.

8. Copy and paste the following code into the new module that is open
on
the screen:

Sub RecoverCorruptDB()
Dim dbCorrupt As DAO.Database
Dim dbCurrent As DAO.Database
Dim td As DAO.TableDef
Dim tdNew As DAO.TableDef
Dim fld As DAO.Field
Dim fldNew As DAO.Field
Dim ind As DAO.Index
Dim indNew As DAO.Index
Dim qd As DAO.QueryDef
Dim qdNew As DAO.QueryDef
Dim strDBPath As String
Dim strQry As String

'Replace the path below to the path of the corrupted database

strDBPath = "C:\My Documents\Appraisals.mdb"
On Error Resume Next
Set dbCurrent = CurrentDb
Set dbCorrupt = OpenDatabase(strDBPath)
For Each td In dbCorrupt.TableDefs
If Left(td.Name, 4) <> "MSys" Then
strQry = "SELECT * INTO [" & td.Name & "] FROM [" &
td.Name &
"] IN '" & dbCorrupt.Name & "'"
dbCurrent.Execute strQry, dbFailOnError
dbCurrent.TableDefs.Refresh
Set tdNew = dbCurrent.TableDefs(td.Name)

'Recreate the indexes on the table

For Each ind In td.Indexes
Set indNew = tdNew.CreateIndex(ind.Name)
For Each fld In ind.Fields
Set fldNew = indNew.CreateField(fld.Name)
indNew.Fields.Append fldNew
Next
indNew.Primary = ind.Primary
indNew.Unique = ind.Unique
indNew.IgnoreNulls = ind.IgnoreNulls
tdNew.Indexes.Append indNew
tdNew.Indexes.Refresh
Next
End If
Next

'Recreate the queries

For Each qd In dbCorrupt.QueryDefs
If Left(qd.Name, 4) <> "~sq_" Then
Set qdNew = dbCurrent.CreateQueryDef(qd.Name,
qd.SQL)
End If
Next
dbCorrupt.Close
Application.RefreshDatabaseWindow


MsgBox "Procedure Complete."
End Sub

This code should import all tables and queries from the damaged
database
into the current database. Note that this solution does not take
secured
databases into effect, so additional code to create workspaces, etc may
be
needed if the database has been secured.

I hope this helps! If you have additional questions on this topic,
please
reply to this posting.

Regards, Frank Miller
Microsoft Support
Click to expand...
Click to expand...
Click to expand...
 
L

Laurie

Thank you. I fear all may be lost. Thank goodness for backups.


George Nicholson said:
With "On Error Resume Next" at the top of the procedure, you will always get
"Procedure Complete", even if
Set dbCorrupt = OpenDatabase(strDBPath)
failed. And if that line fails, so will everything else up to "Procedure
Complete".

That line could fail for a number of reasons: from 'a password is needed' to
'the file is totally hosed'. I'd suggest commenting out the "On Error..."
line and see what your error messages tell you.

HTH & Good Luck!



Laurie said:
Thank you so much for that.

I think I'm still missing something, however, because I get the message
box
at the end that the procedure is complete, but there are no tables or
queries
in my blank database. Am I missing something?



Douglas J. Steele said:
You're the victim of word-wrap: that SQL statement is supposed to be all
1
line.

Here it is again, with continuation characters to prevent word-wrap:

strQry = "SELECT * INTO [" & td.Name & "] FROM [" & _
td.Name & _
"] IN '" & dbCorrupt.Name & "'"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello,

I am very new to VBA. I also have a corrupted database on my hands.

When trying to open the db, we get this message:
"The database is in an unexpected state. Microsoft Access can't open
it.
This database has been converted from a prior version of MS Access by
using
the DAO compact database method instead of the convert database command
on
the tools menu (database utilities sub menu). This has left the
database
in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

We have not converted it from a previous format, it was created in
Access
2003 and is still in Access 2003. However, our we have been having
severe
problems with our network, and I suspect that this is the problem.

Nothing I have tried has worked-- I've tried to import the tables into
a
brand new database, and we get the same message.

I found the posting below, but I don't understand one piece of the
code.
This part, "strQry = "SELECT * INTO [" & td.Name & "] FROM [" & td.Name
&
"] IN '" & dbCorrupt.Name & "'", shows in red. Am I supposed to enter
the
names of my tables/database here?

Thank you for any assistance,
Laurie


http://groups.google.com/group/micr...636c/7e3f15c638abe4e7?&hl=en#7e3f15c638abe4e7

My name is Frank Miller. Thank you for using the Microsoft Access
Newsgroups.

Simply opening the database file would not corrupt it unless the file
was
being opened in another application such as Word, which would
definitely
corrupt the database.

When opening a database in the Access user interface, if we receive the
error...

"The database is in an unexpected state. Microsoft Access can't open
it.
This database has been converted from a prior version of MS Access by
using
the DAO compact database method instead of the convert database command
on
the tools menu (database utilities sub menu). This has left the
database
in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

..we may not be able to convert the database. This message is usually
due
to some corruption that has occurred in the database's project (forms,
reports, macros, modules).

To address this issue we should use standard corruption troubleshooting
to
see if the problem can be corrected. However, more than likely, all
attempts to recover it will fail with the same error. In some cases,
you
can recover the Jet database objects (tables and queries) by creating a
new
database and using DAO code to bring the objects over. Even though you
cannot save the project items, at least the user may be able to recover
their data and queries. To use this technique, follow these steps:

1. Make a backup copy of the original database.

2. Start Microsoft Access.

3. Create a new, blank database.

4. Press ALT+F11 or on the Insert menu, click Module to launch the
Visual
Basic Editor in a separate window, and will create a new module for
you.

5. On the Tools menu, click References. This will display a References
dialog box.

6. Scroll down through the list, and check the box next to "Microsoft
DAO
3.6 Object Library".

7. Click OK to close the References dialog box.

8. Copy and paste the following code into the new module that is open
on
the screen:

Sub RecoverCorruptDB()
Dim dbCorrupt As DAO.Database
Dim dbCurrent As DAO.Database
Dim td As DAO.TableDef
Dim tdNew As DAO.TableDef
Dim fld As DAO.Field
Dim fldNew As DAO.Field
Dim ind As DAO.Index
Dim indNew As DAO.Index
Dim qd As DAO.QueryDef
Dim qdNew As DAO.QueryDef
Dim strDBPath As String
Dim strQry As String

'Replace the path below to the path of the corrupted database

strDBPath = "C:\My Documents\Appraisals.mdb"
On Error Resume Next
Set dbCurrent = CurrentDb
Set dbCorrupt = OpenDatabase(strDBPath)
For Each td In dbCorrupt.TableDefs
If Left(td.Name, 4) <> "MSys" Then
strQry = "SELECT * INTO [" & td.Name & "] FROM [" &
td.Name &
"] IN '" & dbCorrupt.Name & "'"
dbCurrent.Execute strQry, dbFailOnError
dbCurrent.TableDefs.Refresh
Set tdNew = dbCurrent.TableDefs(td.Name)

'Recreate the indexes on the table

For Each ind In td.Indexes
Set indNew = tdNew.CreateIndex(ind.Name)
For Each fld In ind.Fields
Set fldNew = indNew.CreateField(fld.Name)
indNew.Fields.Append fldNew
Next
indNew.Primary = ind.Primary
indNew.Unique = ind.Unique
indNew.IgnoreNulls = ind.IgnoreNulls
tdNew.Indexes.Append indNew
tdNew.Indexes.Refresh
Next
End If
Next

'Recreate the queries

For Each qd In dbCorrupt.QueryDefs
If Left(qd.Name, 4) <> "~sq_" Then
Set qdNew = dbCurrent.CreateQueryDef(qd.Name,
qd.SQL)
End If
Next
dbCorrupt.Close
Application.RefreshDatabaseWindow


MsgBox "Procedure Complete."
End Sub

This code should import all tables and queries from the damaged
database
into the current database. Note that this solution does not take
secured
databases into effect, so additional code to create workspaces, etc may
be
needed if the database has been secured.

I hope this helps! If you have additional questions on this topic,
please
reply to this posting.

Regards, Frank Miller
Microsoft Support
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
WORD VBA Search and switch to advanced ... 1
Make Table in Back End using VBA code is not creating the table 3
WordBasic to VBA conversion 3
Removing OrderBy from Tables and Queries in VBA 2
Dev Ashish Relink Tables 9
copy table structure and queries to new database VBA 1
path of related tables in VBA 2

Top