Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code for exporting to DB suggested by Charlotte (Access 2000)

    Exporting forms in external database

    I refer to the wonderful code suggested to me by Charlotte for importing forms
    regarless of their number.It is an elegenat solution indeed and works perfect!
    It suits entirely to my requirements.
    My question is :
    1 I need also to export the forms beside importing. how can i modify the code to export the forms from an FrontEndDB to an BackendDB.( the opposite action) FrontEnd is the DB i am working in the moment and i want to export the forms to the BackEndDB.I have written acExport in place of acImport,but i receive errors.
    2.How can i import the tables and the queries in the same way? Also,i do not want to import and export the hidden system tables.There is a property in access called attributes= 0 which means user defined tables in the TableDef,but i cannot appply it.It should be somehow in the following order
    For each tbl in BackDb.TableDefs
    If Tbl.Attributes = 0 Then
    ...proceed
    The code i have from by Charlotte,which works excellent for importing, is the following

    Date: 24 Sep 2001 00:21:53 -0000
    charlotte replied to your post
    It's possible either way. With DAO, forms are members of the Forms collection of the Database object. With ADO, they're members of the AllForms collection of the CurrentProject object. Here's a code routine
    (Access 2000 and higher only) that will import all the form object from another Access 2000 database into the current one, but be warned that this is not a fast process except on a very fast machine.

    Function ImportAllForms(ByVal strDBName As String)
    'created by Charlotte Foust 9/23/2001
    Dim appAccess As Access.Application
    Dim frm As AccessObject
    On Error GoTo Proc_err

    'open a new instance of Access
    Set appAccess = New Access.Application
    'open the passed database name, not exclusive
    appAccess.OpenCurrentDatabase strDBName, False

    'Loop through the AllForms collection of the database
    For Each frm In appAccess.CurrentProject.AllForms

    'import each form into the current database
    DoCmd.TransferDatabase TransferType:=acImport, _
    DatabaseType:="Microsoft Access", _
    DatabaseName:=strDBName, _
    ObjectType:=acForm, _
    Source:=frm.name, _
    Destination:=frm.name & "New"
    Next frm ' In appAccess.CurrentProject.AllForms

    Proc_exit:
    On Error Resume Next
    'cleanup and exit
    appAccess.CloseCurrentDatabase
    Set appAccess = Nothing
    Exit Function
    Proc_err:
    MsgBox Err.Number & "--" & Err.Description
    Resume Proc_exit
    End Function 'ImportAllForms(ByVal strDBName As String)

    Charlotte
    Moderator: Access, VB/VBA, eZines

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

    Re: code for exporting to DB suggested by Charlotte (Access 2000)

    Using ADO, you can't check for system tables because the attribute is not exposed by the Jet OLEDB provider that I've been able to find, so you can't use the CurrentProject.AllForms collection to do it and check for the attribute (by the way, Attribute is a bit and I've never seen it checked that way before). You'll have to use DAO, or check the file names for anything like "MSys", or else open a recordset based on the MSysObjects table for objects where [Type] = 1 and [Flags] = -2147483648. Besides, you aren't trying to export tables, you're exporting forms and there aren't any system forms.

    You don't open a new instance of Access to export objects. You export from the current database to a target database, so you don't need to actually look inside the target database at all. Just do a straight TransferDatabase with an acExport transfer type and giving it the name of the target database and the name of the form in the current and target databases.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for exporting to DB suggested by Charlotte (Access 2000)

    Dear Charlotte,

    Please help me once again.I have followed your advices but somewhow i cant built the export variant.Will you be so kind
    to look in my modified code and see where my error lie.I receive the message "you can't delete the database object "Forms1"
    while it is open" Form1 is the form from where i have the command control.
    The code that doesnt function is the following

    Public Function ExportAllForms (ByVal strDBName As String)
    Dim appAccess As AccessObject
    set appAccess.OpenCurrentDatabase strDBName, False
    'loop
    For each frm In App.Access.CurrentProject.AllForms
    DoCmd.TransferDatabase acExport,"Microsoft Access", strDBName, acForm,frm.Name,frm.Name
    Next frm
    app.Access = Nothing
    End Function

    Thank you in advance fro your help.

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

    Re: code for exporting to DB suggested by Charlotte (Access 2000)

    I told you before, you don't open another session of Access to export objects from the current database. All you do is this:

    For each frm In CurrentProject.AllForms
    DoCmd.TransferDatabase acExport,"Microsoft Access", strDBName, acForm,frm.Name,frm.Name
    Next frm

    You're exporting forms from *this* database to another one, so that's all you have to do.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for exporting to DB suggested by Charlotte (Access 2000)

    DearCharlotte,

    Thank you for you reply.I am so sorry,i must be doing some grave mistake so i cannot carry out the code.
    Please dont be angry with me.It is due my lack of basic understandings.
    I have written doen the following code:

    Public Function (ByVal strDBName AS String)
    For each frm In CurrentProject.AllForms
    DoCmd.TransferDatabase acExport,"Microsoft Access", strDBName, acForm,frm.Name,frm.Name
    Next frm
    End Function

    and i receive the message Run Time error 2008, you can't delete the object :Form1" while it is open.
    There is no deletion in my code and i feel at a loss.Can you help me?
    Best regards

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

    Re: code for exporting to DB suggested by Charlotte (Access 2000)

    What database name are you passing into the routine? You need to pass the full path and database name of the database you want to export to. Make sure you don't have *any* forms open because it can't export an open form. That means that you can't run this from a form unless you either exclude the current form from you code by name or you close the form before the routine actually starts.

    The message sounds like you still have something wrong. The "delete" is because you're trying to overwrite an open form, and that should only happen if you passed in the name of the current database instead of the target database.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code for exporting to DB suggested by Charlotte (Access 2000)

    Dear Charlotte,

    You are right,if the form "Form1" is open than i get the error,otherwise not.Otherwise it works.
    However i need to open the code through an opened form.Is there any other way to do what i want ? What i want to do is to send all my forms to another external database.
    The code i have where i get the message that i have an error is the following:
    Public Function ExportAllForms( DBName As String)
    Dim appAccess As Access.Application
    Dim frm As AccessObject
    For each frm in CurrentProject.AllForms
    DoCmd.Close acForm, "Form1"
    DoCmd.TransferDatabase acExport,"Microsoft Access",DBName,acForm,frm.Name,frm.Name
    next frm

    Please help me,i need that badly

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

    Re: code for exporting to DB suggested by Charlotte (Access 2000)

    First off, you don't need to Dim appAccess As Access.Application. That is used to create another instance of Access so you can look inside another database. Just get rid of it in this routine.

    You can insert a line into your commandbutton Click event to call the external routine to export your forms. Have the first code line set Application.Echo False, which will turn off the screen updating and make the form appear to remain open. Have the next line close the form and then go on to the rest of the routine. At the end of the ExportAllForms routine, use a DoCmd.OpenForm to reopen the original form, if that's what you want to do, and then set Application.Echo True to turn screen painting back on.
    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
  •