sJ said:
Using VBA/ADO from Excel, I need to copy an access database to a new
directory, but just the queries and table structures (Not the data).
Do you really need to use ADO? It would be much easier with DAO. Here's a
simple procedure I just cobbled together to do it:
'-------- start of code --------
Sub CopyDBToNewFolder()
On Error GoTo Err_Handler
Dim strSourceDB As String
Dim strTargetDB As String
Dim appAccess As Object
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
' Constants defined to avoid need to reference Access object library
Const acImport = 0
Const acTable = 0
Const acQuery = 1
'*** MODIFY THESE STATEMENTS ***
strSourceDB = "C:\Your Source Path\YourSourceDB.mdb"
strTargetDB = "C:\Your Target Path\YourTargetDB.mdb"
Set appAccess = CreateObject("Access.Application")
With appAccess
.NewCurrentDatabase strTargetDB
Set db = .DBEngine.OpenDatabase(strSourceDB, False, True)
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
.DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acTable, _
tdf.Name, _
tdf.Name, _
True
End If
Next tdf
For Each qdf In db.QueryDefs
If Left(qdf.Name, 1) <> "~" Then
.DoCmd.TransferDatabase _
acImport, _
"Microsoft Access", _
strSourceDB, _
acQuery, _
qdf.Name, _
qdf.Name
End If
Next qdf
End With
Exit_Point:
On Error Resume Next
If Not appAccess Is Nothing Then
appAccess.Quit acQuitSaveAll
Set appAccess = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End Sub
'-------- end of code --------
Requires:
1. Access installed on the PC that is running the code.
2. Reference to the DAO object library, though one could eliminate that
by using late binding.
Notes:
1. The above code doesn't copy relationships. It would be possible to
add code to do that.
2. It would be possible to write an all-DAO version of this code, that
would not require Access to be installed. That would be more complicated,
though. There is, posted on the Microsoft site somewhere, a procedure to
copy a tabledef using only DAO.
