stephenlecomptejr
asked on
How to open a database in DAO via VB.NET to grab all table, query, form, report and coding module names?
I get an unrecognized database format on any Access database that I try to open with DAO. Am I missing an incorrect syntax on the OpenDatabase part? This happens with any database that I try to open.
Private Sub Assign_Objects(ByVal sDB As String)
m_Tables = New Collection
m_Reports = New Collection
m_Queries = New Collection
m_Forms = New Collection
m_Modules = New Collection
m_Macros = New Collection
Dim sMsg As String
Dim db As dao.Database
Dim ctnr As dao.Container
Dim DAODBEngine_definst As New dao.DBEngine()
db = DAODBEngine_definst.OpenDatabase(sDB, False, False, "")
For Each tbl In db.TableDefs
If Not tbl.Name Like "MSys*" And Not Microsoft.VisualBasic.Left(tbl.Name, 3) = "~sq" Then
lValueCount = lValueCount + 100
pbUpdate.Value = lCounter + lValueCount
sMsg = "Assigning table: " & tbl.Name
Msg_This(sMsg)
m_Tables.Add(Item:=tbl.Name)
End If
Next
ctnr = db.Containers("Reports")
For Each rpt In ctnr.Documents
If Not Microsoft.VisualBasic.Left(rpt.Name, 3) = "~sq" Then
lValueCount = lValueCount + 100
pbUpdate.Value = lCounter + lValueCount
sMsg = "Assigning report: " & rpt.Name
Msg_This(sMsg)
m_Reports.Add(Item:=rpt.Name)
End If
Next
For Each qdf In db.QueryDefs
If Not qdf.Name Like "MSys*" And Not Microsoft.VisualBasic.Left(qdf.Name, 3) = "~sq" Then
lValueCount = lValueCount + 100
pbUpdate.Value = lCounter + lValueCount
sMsg = "Assigning query: " & qdf.Name
Msg_This(sMsg)
m_Queries.Add(Item:=qdf.Name)
End If
Next
For Each doc In db.Containers("Forms").Documents
If Not Microsoft.VisualBasic.Left(doc.Name, 3) = "~sq" Then
lValueCount = lValueCount + 100
pbUpdate.Value = lCounter + lValueCount
sMsg = "Assigning form: " & doc.Name
Msg_This(sMsg)
m_Forms.Add(Item:=doc.Name)
End If
Next doc
ctnr = Nothing
db.Close()
db = Nothing
Exit Sub
End Sub
ASKER
When all I have is Microsoft Access 12.0 Object LIbrary as a reference - I get type 'dao.database is not definied per image file.
When I add the Microsoft DAO 3.6 Object Library - I get no build errors but the same error -
unrecognized database format.
dao-not-working.png
When I add the Microsoft DAO 3.6 Object Library - I get no build errors but the same error -
unrecognized database format.
dao-not-working.png
Here is some code (regular VBA, not VB.NET, but it may be helpful) for opening another database (note that for some purposes you need an Access Application variable, for others a DAO Database variable):
Public Sub OpenAnotherDatabase()
'Created by Helen Feddema 14-Feb-2010
'Last modified by Helen Feddema 14-Feb-2010
Dim appAccess As New Access.Application
Dim strDBNameAndPath As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dbe As DAO.DBEngine
'Change to your db name and path
strDBNameAndPath = "G:\Documents\Access 2002-2003 Databases\General.mdb"
appAccess.Visible = True
appAccess.OpenCurrentDatabase filepath:=strDBNameAndPath, _
exclusive:=False
'Run a procedure
'appAccess.Run "PrintOrdersReport"
'Run a macro
'appAccess.DoCmd.RunMacro "mcrPrintOrdersReport"
'Run an action query
'appAccess.DoCmd.OpenQuery "qryDeleteSomeOrders"
'Run SQL code
strSQL = "DELETE tblOrders.ShippedDate FROM tblOrders WHERE ShippedDate = #8/4/1994#;"
Debug.Print "SQL string: " & strSQL
'appAccess.DoCmd.RunSQL strSQL
'Iterate through a recordset
Set dbe = appAccess.DBEngine
Set dbs = dbe.OpenDatabase(strDBNameAndPath)
Set rst = dbs.OpenRecordset("tblCategories")
Do Until rst.EOF
Debug.Print rst![CategoryName]
rst.MoveNext
Loop
rst.Close
Set dbs = Nothing
Set appAccess = Nothing
End Sub
As far as getting table and other object names is concerned, you can use the DAO Database TableDefs and QueryDefs collections to get the names of tables and queries, and the Access Application.CurrentProject 's AllForms, AllMacros, AllModules and AllReports collections for those object names.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You mean this one.
Office-DAO-problem.png
Office-DAO-problem.png
ASKER
Thank you very much for the help!
Microsoft Office 12.0 Access database engine Object Library
Which is the "acedao.dll" file.
BTW, this would be a better solution than your other question.