CreateDatabase & Application.CompactRepair

[Masasar]

Hello,



I have a function that creates a database and adds new tables.

When I open the created database for the first time, it runs an automatic repair.

I am trying to get my front-end database to link to the new database via VBA, but the created database needs to run through the repair first in order for it link.

Therefore, I am trying to get VBA to automatically run the repair, but I am having some trouble getting it to work.

Here is the code to create the new DB:



Function Asdf()


Dim ws As Workspace
Dim db_MSP As Database
Dim Dsk_MSP_DB As String
Dim Recon_MSP_DB As String

Set ws = DBEngine.Workspaces(0)

Dsk_MSP_DB = "C:\Documents and Settings\" & Environ$("Username") & "\Desktop\Reports.accdb"

Recon_MSP_DB = "C:\Documents and Settings\" & Environ$("Username") & "\Desktop\ReportsXX.accdb"

If Dir(Dsk_MSP_DB) <> "" Then Kill Dsk_MSP_DB

Set db_MSP = ws.CreateDatabase(Dsk_MSP_DB, dbLangGeneral)

db_MSP.Execute "CREATE TABLE [Client_C3C6]" & _
"([REPORT_DATE] DATETIME)"

db_MSP.Close

Set db_MSP = Nothing

Application.CompactRepair Dsk_MSP_DB, Recon_MSP_DB


End Function





When I get to 'Application.CompactRepair Dsk_MSP_DB, Recon_MSP_DB' I get a run-time error 31523- unable to open the file. However, If I oepn the created database, have it run the repair, and then run just the 'Application.CompactRepair' step in debug mode- it works.





I have tried using 'DBEngine.CompactDatabase', but it doesn't repair the database in a way that makes the linked tables work.




My code for linking to the created database works, because if I manually open the created database, have it run its automatic repair, then close it and run the code, everything links correctly.




Any ideas on what I am doing wrong, or other methods for repairing a DB in VBA?

Thanks!

[June7]

I tested code that creates a db and links to table without first opening the new db. No 'repair and compact' code.

Sub NewDB()
Dim strExtract As String
Dim Catalog As Object
Dim tdf As TableDef
Dim db As DAO.Database
strExtract = gstrBasePath & "Program\Editing\ConstructionExtract.accdb"
'create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strExtract & ";"
'import table to the ConstructionExtract Access file
CurrentDb.Execute "SELECT * INTO BituminousMD IN '" & strExtract & "' FROM ConstructionBMD;"
'set table link
Set tdf = CurrentDb.CreateTableDef("BituminousMD")
tdf.SourceTableName = "BituminousMD"
tdf.Connect = "; DATABASE=" & gstrBasePath & "Program\Editing\ConstructionExtract.accdb"
CurrentDb.TableDefs.Append tdf
'only way I can get the new table link to show in navigation pane without having to run Compact & Repair or close/reopen db
SendKeys "{ESC}", False
DoCmd.RunCommand acCmdLinkedTableManager
End Sub

[Masasar]

Thanks for your response. That code will be a good reference in the future, but it didn't seem to capture what I was trying to do. However, your mentioning you got it to link without repairing got me to tinkering with it a bit more, and I finally came up with a solution.

Sub NewDB()
Dim strExtract As String
Dim Catalog As Object
Dim db_MSP As DAO.Database
Dim dbCurrent As DAO.Database

'Set new DB string
strExtract = "C:\Documents and Settings\" & Environ$("Username") & "\Desktop\Reports.accdb"

'create new DB
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strExtract & ";"

'Set new DB and create its table(s)
Set db_MSP = DBEngine.Workspaces(0).OpenDatabase(strExtract)
db_MSP.Execute "CREATE TABLE [Client_C3C6]" & _
"([REPORT_DATE] DATETIME)"
db_MSP.Close
Set db_MSP = Nothing

'Link current DB with created DB

         ConnectOutput CurrentDb, _
            "LinkedTable", _
            ";DATABASE=C:\Documents and Settings\" & Environ$("Username") & "\Desktop\Reports.accdb", _
            "Client_C3C6"
 
End Sub



Sub ConnectOutput(dbsTemp As Database, _
   strTable As String, strConnect As String, _
   strSourceTable As String)
   Dim tdfLinked As TableDef

   ' Create a new TableDef, set its Connect and
   ' SourceTableName properties based on the passed
   ' arguments, and append it to the TableDefs collection.

   Set tdfLinked = dbsTemp.CreateTableDef(strTable)
   tdfLinked.Connect = strConnect
   tdfLinked.SourceTableName = strSourceTable
   dbsTemp.TableDefs.Append tdfLinked

End Sub