How to Open database in a different folder

[Stapes]

Hi

I am using the following code to open a database:

Set db = OpenDatabase(strDatabase)

Trouble is, the database I want to open here has a different filepath
to the one I am using. How do I get it to open a database in a
different folder?

Stapes

 

[Tom Wickerath]

And strDatabase is set to what? The name of the database only, or the
complete path including filename?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Stapes]

And strDatabase is set to what? The name of the database only, or the
complete path including filename?

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________









- Show quoted text -

strDatabase is set to C:\Documents and Settings\stephen\Desktop
\Pocketbooks\Frontend\21Nov2007\Management Pocketbooks FE.mdb
The program keeps crashing at the line marked ** in my code (below)
with the error : 3011 - The Microsoft Jet database could not find the
object 'Book Titles'.

OK - here is my code:

Public Sub ExportDatabaseObjects(strProject As String, strFilePath As
String)
On Error GoTo Err_ExportDatabaseObjects

Dim db As Database
'Dim db As DAO.Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String
Dim strDatabase As String
strDatabase = strFilePath

'Set db = CurrentDb()
Set db = OpenDatabase(strDatabase)

sExportLocation = "C:\AccessSourceControl\" & strProject & "\" 'Do not
forget the closing back slash! ie: C:\Temp\

For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , td.Name, sExportLocation &
"Table_" & td.Name & ".txt", True **
End If
Next td

Set c = db.Containers("Forms")
For Each d In c.Documents
Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" &
d.Name & ".txt"
Next d

Set c = db.Containers("Reports")
For Each d In c.Documents
Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" &
d.Name & ".txt"
Next d

Set c = db.Containers("Scripts")
For Each d In c.Documents
Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" &
d.Name & ".txt"
Next d

Set c = db.Containers("Modules")
For Each d In c.Documents
Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" &
d.Name & ".txt"
Next d

For i = 0 To db.QueryDefs.Count - 1
Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation
& "Query_" & db.QueryDefs(i).Name & ".txt"
Next i

Set db = Nothing
Set c = Nothing

MsgBox "All database objects have been exported as a text file to " &
sExportLocation, vbInformation

Exit_ExportDatabaseObjects:
Exit Sub

Err_ExportDatabaseObjects:
MsgBox Err.Number & " - " & Err.Description

'Resume Exit_ExportDatabaseObjects
Resume Next
End Sub

 

[Douglas J. Steele]

While you've instantiated db as an external database, the rest of your
commands (DoCmd.TransferText and Application.SaveAsText) refer to the
current database, not the external one.

You actually need to instantiate an instance of Access, set its
CurrentDatabase appropriate, and then use that instance, not the current
one:

Public Sub ExportDatabaseObjects( _
strProject As String, _
strFilePath As String)
On Error GoTo Err_ExportDatabaseObjects

Dim app As Access.Application
Dim db As DAO.Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String
Dim strDatabase As String
strDatabase = strFilePath

Set db = OpenDatabase(strDatabase)

Set app = New Access.Application
app.OpenCurrentDatabase strDatabase

sExportLocation = "C:\AccessSourceControl\" & strProject & "\" 'Do not
forget the closing back slash! ie: C:\Temp\

For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
app.DoCmd.TransferText acExportDelim, , td.Name, _
sExportLocation & "Table_" & td.Name & ".txt", True **
End If
Next td

Set c = db.Containers("Forms")
For Each d In c.Documents
app.SaveAsText acForm, d.Name, _
sExportLocation & "Form_" & d.Name & ".txt"
Next d

Set c = db.Containers("Reports")
For Each d In c.Documents
app.SaveAsText acReport, d.Name, _
sExportLocation & "Report_" & d.Name & ".txt"
Next d

Set c = db.Containers("Scripts")
For Each d In c.Documents
app.SaveAsText acMacro, d.Name, _
sExportLocation & "Macro_" & d.Name & ".txt"
Next d

Set c = db.Containers("Modules")
For Each d In c.Documents
app.SaveAsText acModule, d.Name, _
sExportLocation & "Module_" & d.Name & ".txt"
Next d

For i = 0 To db.QueryDefs.Count - 1
app.SaveAsText acQuery, db.QueryDefs(i).Name, _
sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
Next i

MsgBox "All database objects have been exported as a text file to " &
sExportLocation, vbInformation


app.CloseCurrentDatabase

Exit_ExportDatabaseObjects:
Set app = Nothing
Set db = Nothing
Set c = Nothing
Exit Sub

Err_ExportDatabaseObjects:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ExportDatabaseObjects

End Sub

 

[Stapes]

While you've instantiated db as an external database, the rest of your
commands (DoCmd.TransferText and Application.SaveAsText) refer to the
current database, not the external one.

You actually need to instantiate an instance of Access, set its
CurrentDatabase appropriate, and then use that instance, not the current
one:

Public Sub ExportDatabaseObjects( _
strProject As String, _
strFilePath As String)
On Error GoTo Err_ExportDatabaseObjects

Dim app As Access.Application
Dim db As DAO.Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String
Dim strDatabase As String
strDatabase = strFilePath

Set db = OpenDatabase(strDatabase)

Set app = New Access.Application
app.OpenCurrentDatabase strDatabase

sExportLocation = "C:\AccessSourceControl\" & strProject & "\" 'Do not
forget the closing back slash! ie: C:\Temp\

For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
app.DoCmd.TransferText acExportDelim, , td.Name, _
sExportLocation & "Table_" & td.Name & ".txt", True **
End If
Next td

Set c = db.Containers("Forms")
For Each d In c.Documents
app.SaveAsText acForm, d.Name, _
sExportLocation & "Form_" & d.Name & ".txt"
Next d

Set c = db.Containers("Reports")
For Each d In c.Documents
app.SaveAsText acReport, d.Name, _
sExportLocation & "Report_" & d.Name & ".txt"
Next d

Set c = db.Containers("Scripts")
For Each d In c.Documents
app.SaveAsText acMacro, d.Name, _
sExportLocation & "Macro_" & d.Name & ".txt"
Next d

Set c = db.Containers("Modules")
For Each d In c.Documents
app.SaveAsText acModule, d.Name, _
sExportLocation & "Module_" & d.Name & ".txt"
Next d

For i = 0 To db.QueryDefs.Count - 1
app.SaveAsText acQuery, db.QueryDefs(i).Name, _
sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
Next i

MsgBox "All database objects have been exported as a text file to " &
sExportLocation, vbInformation

app.CloseCurrentDatabase

Exit_ExportDatabaseObjects:
Set app = Nothing
Set db = Nothing
Set c = Nothing
Exit Sub

Err_ExportDatabaseObjects:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ExportDatabaseObjects

End Sub

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)























- Show quoted text -

Fantastic. And thanks. Now I can run my Source Control procedures from
one place.