Group: microsoft.public.access
From: =?Utf-8?B?UHJvZ3JhbW1lciAtIHdhbm5hQg==?=
Date: Tuesday, October 23, 2007 10:38 AM
Subject: Re: VBA to import 344 objects from 97 to 2003

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") =
Set Cont = Db.Containers("Form")

' The next 2 lines show values =
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" wrote:
> Nearly there
>
> 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" wrote in
> message news:4BC07EB5-6FCB-472B-9410-34663506C395@microsoft.com...
> >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" wrote:
> >
> >> 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" wrote
> >> in
> >> message news:203DA117-0858-4BE7-A78A-2ADE14BF6C14@microsoft.com...
> >> >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" wrote:
> >> >
> >> >>
> >> >> 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