Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import all objects (Access97)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    I'm trying to import all the objects from one database into another using code.
    I've played around with the TransferDatabase method and looping through all the objects.
    What I get is a runtime error 13 Type Mismatch
    Any Ideas? Here is the code
    Dim strSQL As String
    Dim rs As Recordset
    Dim db As Database

    Dim dbs As Database, fld As Field, rst As Recordset
    Dim targetws As Workspace, targetdb As Database, MyDB As Database
    Dim targetss As Snapshot, OBJ As String
    Dim strNME As String, strOBJ As String

    Set targetws = DBEngine.Workspaces(0)
    Set targetdb = targetws.OpenDatabase("c:reliability_upgrade.mdb")
    Set dbs = targetdb
    strSQL = "SELECT MsysObjects.Name, IIf([TYPE]=-32768,'acForm',IIf([TYPE]=-32766,'acMacro', " & _
    "IIf([TYPE]=-32764,'acReport',IIf([TYPE]=5,'acQuery',IIf([TYPE]=-32761,'acModule', " & _
    "IIf([TYPE]=1,'acTable')))))) AS OBJ, MsysObjects.Type " & _
    "FROM MSYSOBJECTS " & _
    "WHERE (((MsysObjects.Name) Not Like 'msys*' And (MsysObjects.Name)<>'FRMUPDATE') AND " & _
    "((MsysObjects.Type)<>2 And (MsysObjects.Type)<>3 And (MsysObjects.Type)<>6 And " & _
    "(MsysObjects.Type)<>-32757) AND ((Left$([Name],1))<>'~')) " & _
    "ORDER BY MsysObjects.Name;"

    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveLast
    rst.MoveFirst
    Do Until rst.EOF
    strOBJ = rst!OBJ
    strNME = rst!NAME

    DoCmd.TransferDatabase acImport, "MICROSOFT ACCESS", "C:RELIABILITY_UPGRADE.MDB", strOBJ, strNME
    rst.MoveNext
    Loop

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import all objects (Access97)

    I found out what the problem was
    ObjectType in the TransferDatabase method needed to be a number, not a string (acModule,acForm...)
    I used a Select Case statement to provide the numbers

    Dim strSQL As String
    Dim rs As Recordset
    Dim db As Database
    Dim dbs As Database
    Dim rst As Recordset
    Dim targetws As Workspace
    Dim targetdb As Database
    Dim strNME As String
    Dim strTYPE As String


    ''''DELETES ALL EXISTING OBJECTS EXCEPT TABLES

    Set db = CurrentDb
    strSQL = "SELECT MsysObjects.Name,
    IIf([TYPE]=-32768,'acForm',IIf([TYPE]=-32766,'acMacro',IIf([TYPE]=-32764,'acReport',IIf([TYPE]=5,'acQuery',IIf([TYPE]=-32761,'acModule')))))
    " & _
    "AS Expr1, MsysObjects.Type FROM MsysObjects WHERE (((MsysObjects.Name)<>'frmUPGRADE') AND ((MsysObjects.Type)<>1 And
    (MsysObjects.Type)<>2 And (MsysObjects.Type)<>3 And (MsysObjects.Type)<>6 And (MsysObjects.Type)<>-32757) AND
    ((Left$([Name],1))<>'~')) " & _
    "ORDER BY MsysObjects.Name;"

    Set rs = db.OpenRecordset(strSQL)
    rs.MoveFirst
    Do While Not rs.EOF
    Select Case rs(1)
    Case "acMacro"
    DoCmd.DeleteObject acMacro, rs(0)
    Case "acForm"
    DoCmd.DeleteObject acForm, rs(0)
    Case "acQuery"
    DoCmd.DeleteObject acQuery, rs(0)
    Case "acReport"
    DoCmd.DeleteObject acReport, rs(0)
    Case "acModule"
    DoCmd.DeleteObject acModule, rs(0)
    End Select
    rs.MoveNext
    Loop
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "tblVERSION"
    DoCmd.SetWarnings True

    ''''IMPORTES NEW OBJECTS FROM MODIFIED DB
    Set targetws = DBEngine.Workspaces(0)
    Set targetdb = targetws.OpenDatabase("c:reliability_upgrade.mdb")
    Set dbs = targetdb 'REFERENCE TO THE EXTERNAL DB

    strSQL = "SELECT MsysObjects.Name, IIf([TYPE]=-32768,'acForm',IIf([TYPE]=-32766,'acMacro', " & _
    "IIf([TYPE]=-32764,'acReport',IIf([TYPE]=5,'acQuery',IIf([TYPE]=-32761,'acModule', " & _
    "IIf([TYPE]=1,'acTable')))))) AS OBJ, MsysObjects.Type " & _
    "FROM MSYSOBJECTS " & _
    "WHERE (((MsysObjects.Name) Not Like 'msys*' And (MsysObjects.Name)<>'FRMUPDATE') AND " & _
    "((MsysObjects.Type)<>2 And (MsysObjects.Type)<>3 And (MsysObjects.Type)<>6 And " & _
    "(MsysObjects.Type)<>-32757) AND ((Left$([Name],1))<>'~')) " & _
    "ORDER BY MsysObjects.Name;"

    Set rst = dbs.OpenRecordset(strSQL) 'IDENTIFIES ALL OBJECTS IN THE EXTERNAL DB TO IMPORT

    Do Until rst.EOF
    Select Case rst!OBJ 'SUPPLIES THE VALUE OF OBJECTTYPE AS A NUMBER
    Case "acForm"
    strTYPE = 2
    Case "acMacro"
    strTYPE = 4
    Case "acModule"
    strTYPE = 5
    Case "acQuery"
    strTYPE = 1
    Case "acReport"
    strTYPE = 3
    Case "acTABLE"
    strTYPE = 0
    End Select
    strNME = rst!NAME
    DoCmd.TransferDatabase acImport, "MICROSOFT ACCESS", "C:RELIABILITY_UPGRADE.MDB", strTYPE, strNME, strNME
    rst.MoveNext
    Loop
    targetdb.Close
    MsgBox "Done"
    Kill "c:reliability_upgrade.mdb" 'DELETE EXTERNAL DB

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import all objects (Access97)

    The built-in constants acForm, etc., *are* numbers. You put single quotes around them in your code instead of using them au naturale the way you should have. You don't need code to convert them, just lose the quotes.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •