VBA to import 344 objects from 97 to 2003

[Programmer - wannaB]

I am looking to import a select list of tables, forms, queries, reports and
macros from Access97 into Access2003. Yes this can be done by selecting each
individual object, but I need to do this 5 or 6 more times before I do it for
the final production copy.

Pieter, Ken, and John have all been great resources in previous VBA
questions, I would greatly appreciate any/all assistance. Thank you!!!

 

[Douglas J. Steele]

The import dialog lets you select all items on the form. Since you're only
talking 5 or 6 times, it's probably faster to do that, rather than bothering
to write code to do it.

 

[Programmer - wannaB]

The Import dialog provides buttons to SELECT ALL or DESELECT ALL, for each
?module? / ?tab?. But I DO NOT WANT ALL, I need to select 144 of 255
reports, 84 of 366 queries, and about 10 percent of the remaining objects,
you get my point... This is a very old DB that has been too accessable to
too many "dangerous" user's, and I have spent many weeks gathering info about
what is actually in use/needed and what can be left behind.

It would be simple to paste this list into some code that would automate the
import selection process. PLUS, this would be a great learning experience
for VBA development. Thank you.

 

[Douglas J. Steele]

You can use the TransferDatabase method to export from the current database:

DoCmd.TransferDatabase acExport, _
"Microsoft Access", _
"C:\Folder\File.mdb", _
acForm, _
"NameOfForm", _
"NameOfForm"

Instead of acForm, you can also use acMacro, acModule, acQuery, acReport or
acTable.

 

[David]

One of the most important lessons that I've learned is when NOT to use VBA
code, generally when there are simpler ways to achieve a given goal.

Wouldn't it be much simpler it create a new blank MDB file, import all the
the desired objects into that, then just import them from that intermediary
MDB file the 5 or 6 times into other databases as needed, using the SELECT
ALL button?

DBS

 

[Pieter Wijnen]

Building on the Code I gave you earlier today you can make a table
containing 2 Fields: Name & Type From MSysObjects
You can then delete all unwanted object references from this table.

You can use this function to convert container name to the ObjectType as
used by DoCmd.TransferDatabase (a.o)
Note: This Does not distinguish between Tables & Queries

Private Function GetObjType(ContName As String) As Access.AcObjectType
' There's no container named Queries - so this requires that the object is
typechecked for this to return the correct object type
On Local Error Resume Next

Select Case ContName
Case "Tables":
GetObjType = Access.AcObjectType.acTable
Case "Scripts":
GetObjType = Access.AcObjectType.acMacro
Case "Forms":
GetObjType = Access.AcObjectType.acForm
Case "Modules":
GetObjType = Access.AcObjectType.acModule
Case "Queries":
GetObjType = Access.AcObjectType.acQuery
Case "Reports":
GetObjType = Access.AcObjectType.acReport
End Select
End Function

The Types in MSysObjects are as follows

Private Enum thObjType
thTable = 1
thTableAttached = 4
thQuery = 5
thMacro = -32766
thForm = -32768
thModule = -32761
thReport = -32764
End Enum


HTH

Pieter

 

[WANNABE]

Thank you Pieter. I can follow a little of this, but I get lost un a few
places... PLEASE see questions/comments in text..

Building on the Code I gave you earlier today you can make a table
containing 2 Fields: Name & Type From MSysObjects
You can then delete all unwanted object references from this table.
FIELDNAME FOR NAME IN THE NEW TABLE?
You can use this function to convert container name to the ObjectType as
METHOD 1 OR 2?
used by DoCmd.TransferDatabase (a.o)
Note: This Does not distinguish between Tables & Queries
Private Function GetObjType(ContName As String) As Access.AcObjectType
' There's no container named Queries - so this requires that the object is
typechecked for this to return the correct object type
On Local Error Resume Next

Select Case ContName
Case "Tables":
GetObjType = Access.AcObjectType.acTable
Case "Scripts":
GetObjType = Access.AcObjectType.acMacro
Case "Forms":
GetObjType = Access.AcObjectType.acForm
Case "Modules":
GetObjType = Access.AcObjectType.acModule
Case "Queries":
GetObjType = Access.AcObjectType.acQuery
Case "Reports":
GetObjType = Access.AcObjectType.acReport
End Select
End Function
The Types in MSysObjects are as follows

Private Enum thObjType
thTable = 1
thTableAttached = 4
thQuery = 5
thMacro = -32766
thForm = -32768
thModule = -32761
thReport = -32764
End Enum


HTH

Pieter

 

[Pieter Wijnen]

Partial code (see text also)

Set Rs = Db.OpenRecordset("SELECT NAME, TYPE FROM
USysObjects",DAO.DbOpenSnapshot)
While Not Rs.EOF
Select Case Rs.Fields("Type").Value
Case thTable
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thQuery
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType("Queries")
End Select
Access.Application.DoCmd.TransferDatabase
Access.AcDataTransferType.acExport, "Microsoft Access", ToDb.Name, ObjType,
Doc.Name, Doc.Name
'...
Wend

HtH

Pieter

Thank you Pieter. I can follow a little of this, but I get lost un a few
places... PLEASE see questions/comments in text..


Building on the Code I gave you earlier today you can make a table
containing 2 Fields: Name & Type From MSysObjects
You can then delete all unwanted object references from this table.

method 1 is closest with respect to how to do it, method 2 comes into play
with regards to the "how"

FIELDNAME FOR NAME IN THE NEW TABLE? Neither, see later
You can use this function to convert container name to the ObjectType as

METHOD 1 OR 2? No, Example last
used by DoCmd.TransferDatabase (a.o)
Note: This Does not distinguish between Tables & Queries

The container names are Tables, Forms, Reports, Modules & Macros

Private Function GetObjType(ContName As String) As Access.AcObjectType
' There's no container named Queries - so this requires that the object
is
typechecked for this to return the correct object type
On Local Error Resume Next

Select Case ContName
Case "Tables":
GetObjType = Access.AcObjectType.acTable
Case "Scripts":
GetObjType = Access.AcObjectType.acMacro
Case "Forms":
GetObjType = Access.AcObjectType.acForm
Case "Modules":
GetObjType = Access.AcObjectType.acModule
Case "Queries":
GetObjType = Access.AcObjectType.acQuery
Case "Reports":
GetObjType = Access.AcObjectType.acReport
End Select
End Function

+ yes, these are the actual msysobject types

 

[Programmer - wannaB]

I am no where near close, I spent 4 hours looking at this last night, and
this morning and still I am not sure how you would put it all together?? I
did find some very helpful info by Susan Sales Harkins, at
http://www.acontractorsworld.com/visba.htm on the use of Private Enum. But
there is still so much I don't know.

How would you explain what needs to be done to a moron, like me..?
Thanks for all your help
===================

 

[Pieter Wijnen]

Basically you need to :
Create a Table USysObjects (Based on MSysObjects)

declare the variables
Dim Db As DAO.Database
Dim Cont As DAO.Container
Dim Doc As DAO.Document
Dim Rs As DAO.Recordset
Dim ObjType As thObjectType

Fill in the rest of the Types (containers) in the Select Case

Add
Rs.MoveNext (Before Wend)

Add
Rs.Close & Set All Objects to Nothing (Not strictly neccessary, but a good
habit)

Pieter

 

[Programmer - wannaB]

I hope this doesn't make me look at stupid as I feel. But this is what I have
put together from your tips.

*** the Primary Sub which uses a select statement to gather the NAME and TYPE
*** data fron the copy of the MSysObjects table, then the CASE statements use
*** the Enum (whats it called) to ID the container type, but I'm not sure
what
*** the SET deos in each of the CASE's. I think ObjType = uses the Function
*** to set the object type ??? but really i have no idea???
*** HOW FAR OFF AM I ***

Public Sub ObjectsTbl()
Dim Db As DAO.Database
Dim Cont As DAO.Container
Dim Doc As DAO.Document
Dim Rs As DAO.Recordset
Dim ObjType As thObjectType

Set Rs = Db.OpenRecordset("SELECT NAME, TYPE FROM
USysObjects",DAO.DbOpenSnapshot)
While Not Rs.EOF
Select Case Rs.Fields("Type").Value
Case thTable
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thQuery
Set Cont = Db.Containers("Queries")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thMacro
Set Cont = Db.Containers("Macro")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thForm
Set Cont = Db.Containers("Form")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thReport
Set Cont = Db.Containers("Report")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
End Select
Access.Application.DoCmd.TransferDatabase
Access.AcDataTransferType.acExport, "Microsoft Access", To

Db.Name, ObjType, Doc.Name, Doc.Name
Wend
End Sub

Private Function GetObjType(ContName As String) As Access.AcObjectType
' There's no container named Queries - so this requires that the object is
typechecked for this to

return the correct object type
On Error Resume Next

Select Case ContName
Case "Tables":
GetObjType = Access.AcObjectType.acTable
Case "Scripts":
GetObjType = Access.AcObjectType.acMacro
Case "Forms":
GetObjType = Access.AcObjectType.acForm
Case "Modules":
GetObjType = Access.AcObjectType.acModule
Case "Queries":
GetObjType = Access.AcObjectType.acQuery
Case "Reports":
GetObjType = Access.AcObjectType.acReport
End Select
End Function

Private Enum thObjType
thTable = 1
thTableAttached = 4
thQuery = 5
thMacro = -32766
thForm = -32768
thModule = -32761
thReport = -32764
End E
======================================

 

[Pieter Wijnen]

So now you only lack the ToDb?

Dim ToDb As DAO.Database

Set ToDb = Access.DbEngine.CreateDatabase("C:\NewDb.mdb")
Set Db = Access.CurrentDb()

There's *still* no container for queries:
Case thQuery
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType("Queries")

Note: To Import into a 2003 Db, you'd have to reverse the logic as '97 can't
read (or write to) '2003
ie use
Set Rs = Db.OpenRecordset("SELECT NAME, TYPE FROM USysObjects IN '" &
ToDb.Name & "'",DAO.DbOpenSnapshot)
etc

Pieter

 

[Programmer - wannaB]

I am so sorry, that I have not caught on yet .

'After Adding the Dim and changing the SET as suggested, this is what I have
'But I am still not clear on what you meant by
' "There's still no container for queries:"
'Obviously I do not understand how the CASE statement is working,
' I had this is there
' Case thQuery
' Set Cont = Db.Containers("Queries")
' Set Doc = Cont.Documents(Rs.Fields("Name").Value)
' ObjType = GetObjType(Cont.Name)

' And your reply was to use this
' Case thQuery
' Set Cont = Db.Containers("Tables")
' Set Doc = Cont.Documents(Rs.Fields("Name").Value)
' ObjType = GetObjType("Queries")

' Can you Explain PLEASE? - Do I need to change ALL GetObjType(Cont.Name)
' to the container name ??


'- How do all the SUBs and FUNCTIONs tie together?

'When you say I need to reverse the Logic I understand the issue, but I am
'not sure of the sollution that I have implemented. How can I get the VBA
'EDITOR to show me more about how the command works??
' When I start typeing Access.
'I get a list of what could be used next, But once I get to
' .AcImport And I try . OR ( OR , I get no more help about how to
'construct the rest of the statement $%@&%@#*

'How can I get more help from THE VBA editor on how to struct those????


Public Sub ObjectsTbl()
Dim Db As DAO.Database
Dim Cont As DAO.Container
Dim Doc As DAO.Document
Dim Rs As DAO.Recordset
Dim ObjType As thObjectType
Dim ToDb As DAO.Database

Set ToDb = Access.DBEngine.CreateDatabase("D:\Dev\StageingDb.mdb")
Set Db = Access.CurrentDb()
Set Rs = Db.OpenRecordset("SELECT NAME, TYPE FROM USysObjects IN '" &
ToDb.Name & "'", DAO.dbOpenSnapshot)
While Not Rs.EOF
Select Case Rs.Fields("Type").Value
Case thTable
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thQuery
Set Cont = Db.Containers("Queries")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thMacro
Set Cont = Db.Containers("Macro")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thForm
Set Cont = Db.Containers("Form")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thReport
Set Cont = Db.Containers("Report")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
End Select
Access.Application.DoCmd.TransferDatabase
Access.AcDataTransferType.acImport, "Microsoft Access", ToDb.Name, ObjType,
Doc.Name
Wend
End Sub

=======================================================

 

[Pieter Wijnen]

Nearly there <g>

Private Enum thObjType
thTable = 1
thTableAttached = 4
thQuery = 5
thMacro = -32766
thForm = -32768
thModule = -32761
thReport = -32764
End Enum

Private Function GetObjType(ContName As String) As Access.AcObjectType
' There's no container named Queries - so this requires that the object is
typechecked for this to
' return the correct object type
On Error Resume Next

Select Case ContName
Case "Tables":
GetObjType = Access.AcObjectType.acTable
Case "Scripts":
GetObjType = Access.AcObjectType.acMacro
Case "Forms":
GetObjType = Access.AcObjectType.acForm
Case "Modules":
GetObjType = Access.AcObjectType.acModule
Case "Queries":
GetObjType = Access.AcObjectType.acQuery
Case "Reports":
GetObjType = Access.AcObjectType.acReport
End Select
End Function

Public Sub ObjectsTbl()
' Code Modified to Import into access 2003 from Access '97
Dim Db As DAO.Database
Dim Cont As DAO.Container
Dim Doc As DAO.Document
Dim Rs As DAO.Recordset
Dim ObjType As thObjectType
Dim FromDb As DAO.Database

' This should be your access '97 db
Set FromDb = Access.DBEngine.OpenDatabase("D:\Prod\Access97.mdb")
Set Db = Access.CurrentDb()
' Get what's in Access '97 & you want xfered to 2003
Set Rs = FromDb.OpenRecordset("SELECT NAME, TYPE FROM USysObjects,
DAO.dbOpenSnapshot)
While Not Rs.EOF
Select Case Rs.Fields("Type").Value
Case thObjType.thTable
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thObjType.thQuery ' Special Case !!!
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType("QUeries")
Case thObjType.thMacro
Set Cont = Db.Containers("Macro")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thObjType.thForm
Set Cont = Db.Containers("Form")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thObjType.thReport
Set Cont = Db.Containers("Report")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
End Select
Access.Application.DoCmd.TransferDatabase
Access.AcDataTransferType.acImport, "Microsoft Access", FromDb.Name,
ObjType, Doc.Name, Doc.Name
Wend
Rs.Close : Set Rs = Nothing
FromDb.Close : Set FromDb = Nothing
Set Db = Nothing

End Sub

HtH

Pieter

 

[Programmer - wannaB]

Again Thank you for all your Help Pieter.
OK First time running through debugger, and I think I learned a little, BUT
Will you please explain a few of these areas and the ERRORs?

Public Sub ObjectsTbl()
' Code Modified to Import into access 2003 from Access '97
Dim Db As DAO.Database
Dim Cont As DAO.Container
Dim Doc As DAO.Document
Dim Rs As DAO.Recordset
Dim ObjType As Object
Dim FromDb As DAO.Database

' This should be your access '97 db
Set FromDb = Access.DBEngine.OpenDatabase("D:\Dev\StageingDb.mdb")
Set Db = Access.CurrentDb()
' Get what's in Access '97 & you want xfered to 2003

' added " after Usysobjects
Set Rs = FromDb.OpenRecordset("SELECT NAME, TYPE FROM USysObjects",
DAO.dbOpenSnapshot)
While Not Rs.EOF
Select Case Rs.Fields("Type").Value
Case thObjType.thTable
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thObjType.thQuery ' Special Case !!!
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType("QUeries")
Case thObjType.thMacro
Set Cont = Db.Containers("Macro")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)

' The first Record gets to this line, but I see all values do not seem to be
set
Case thObjType.thForm

' The next line is what is desplayed while hovering the cursor over it.
' Db.Containers("Form") = <Item not found in this collection.>
Set Cont = Db.Containers("Form")

' The next 2 lines show values = <Object variable or With block not set>
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)

' What is suppose to happen in each of the 3 statements after the CASE ???
Case thObjType.thReport
Set Cont = Db.Containers("Report")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
End Select

' Modified the next line while debugging and it passes the compile test
Access.Application.DoCmd.TransferDatabase acImport, "Microsoft Access",
FromDb.Name, ObjType, Doc.Name, Doc.Name
Wend
Rs.Close: Set Rs = Nothing
FromDb.Close: Set FromDb = Nothing
Set Db = Nothing
End Sub
===========================

 

[Pieter Wijnen]

I finally imported the code in an actual access App & wound up with

Private Enum thObjType
thTable = 1
thTableAttached = 4
thQuery = 5
thMacro = -32766
thForm = -32768
thModule = -32761
thReport = -32764
End Enum

Private Function GetObjType(ContName As String) As Access.AcObjectType
' There's no container named Queries - so this requires that the object is
typechecked for this to
' return the correct object type
On Error Resume Next

Select Case ContName
Case "Tables":
GetObjType = Access.AcObjectType.acTable
Case "Scripts":
GetObjType = Access.AcObjectType.acMacro
Case "Forms":
GetObjType = Access.AcObjectType.acForm
Case "Modules":
GetObjType = Access.AcObjectType.acModule
Case "Queries":
GetObjType = Access.AcObjectType.acQuery
Case "Reports":
GetObjType = Access.AcObjectType.acReport
End Select
End Function

Public Sub ObjectsTbl()
' Code Modified to Import into access 2003 from Access '97
Dim Db As DAO.Database
Dim Cont As DAO.Container
Dim Doc As DAO.Document
Dim Rs As DAO.Recordset
Dim ObjType As thObjType
Dim FromDb As DAO.Database

' This should be your access '97 db
Set FromDb = Access.DBEngine.OpenDatabase("D:\Prod\Access97.mdb")
Set Db = Access.CurrentDb()
' Get what's in Access '97 & you want xfered to 2003
Set Rs = FromDb.OpenRecordset("SELECT NAME, TYPE FROM USysObjects",
DAO.dbOpenSnapshot)
While Not Rs.EOF
Select Case Rs.Fields("Type").Value
Case thObjType.thTable
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thObjType.thQuery ' Special Case !!!
Set Cont = Db.Containers("Tables")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType("Queries")
Case thObjType.thMacro
Set Cont = Db.Containers("Scripts")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thObjType.thForm
Set Cont = Db.Containers("Forms")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thObjType.thReport
Set Cont = Db.Containers("Reports")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
Case thObjType.thModule
Set Cont = Db.Containers("Modules")
Set Doc = Cont.Documents(Rs.Fields("Name").Value)
ObjType = GetObjType(Cont.Name)
End Select
Access.Application.DoCmd.TransferDatabase
Access.AcDataTransferType.acImport, "Microsoft Access", FromDb.Name,
ObjType, Doc.Name, Doc.Name
Wend
Rs.Close: Set Rs = Nothing
FromDb.Close: Set FromDb = Nothing
Set Db = Nothing


End Sub


Pieter



"Pieter Wijnen"

 

[Programmer - wannaB]

Is there a reference Library that I need OR Are these next 2 lines wrong

At this point in the DEBUG mode I get a Run-time error 3265
Item not found in this collection.

If I hover over this part of the line
Cont.Documents I see the message
Cont.Documents(Rs.Fields("Name").Value) = <Item not found in this collection.>

When I hover over this part of the line
(Rs.Fields("Name").Value) I see the message
Rs.Fields("Name").Value = "Econ"
and that is the first FORM in the list to be imported so I thought

If I changed this line
Set Doc = Cont.Documents(Rs.Fields("Name").Value)

to read like this
Set Doc = Rs.Fields("Name").Value

That would be the answer, NO it wasn't (Maybe I should stop thinking)
that produced the ERROR Run-time error 424 - Object required
========================================

 

[Pieter Wijnen]

my fault again
needs to be FromDb.Containers, as we're after the documents (objects) in the
"foreign" database

Pieter

 

[Programmer - wannaB]

I am just not yet skilled enough to figure these things out, I see what you
found in the last error and I'm embareassed that I didn't see that myself, as
I'm sure that this error will leave me feeling the same...

FIXED THIS TO Set Cont = FromDb.Containers("Forms")
AS DEFINED IN THE DIM FromDb As DAO.Database

Now I get and "Object required" error on the very next line.
Set Doc = Cont.Documents(Rs.Fields("Name").Value)

==================================

 

[Pieter Wijnen]

What object is not defined?
What is the value of Rs.Fields("Name").Value?

Pieter