Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Values for all properties of all controls on a form/report in an ADP

    I just purchased the Access Cookbook 2nd Edition from O'Reilly (which I highly recommend), and I have a question on adapting on of the "recipes" from a .mdb to a .adp. Note: I'm using Access 2003 connected to SQL Server 2000.

    Recipe 4.3 Verify That Objects Use Consistent Settings uses DAO to create several tables and then populate them with the property values of all the controls on the forms or reports that you select in a .mdb file. Unfortunately, I am using an Access Data Project (ADP) so this isn't working for me.

    Can I even use DAO in an ADP?

    Anyway, it gets this far into the procedure
    Code:
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        
        'On Error GoTo HandleErr
        
        Set db = CurrentDb
        
        db.Execute "DROP TABLE zstblInventory"
        ...
    and then I get this error message at the "DROP TABLE..." line:
    "91: Object variable or With block variable not set."

    I am really just looking for a direction like "Learn how to use ADO" or maybe there is a simpler solution.

    Any help would be greatly appreciated. Thank you!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I dunno if you are really dead set on using that particular method, but if your inclined to other options, DoCmd.RunSQL would get the job done fairly cleanly.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Posts
    126
    No, I'm not deadset on using that method. I had just begun to experiment with making an ADO connection instead of the DAO connection.

    If I use DoCmd.RunSQL, would I have to make any connections or anything, or would it just use the ADP's connection to my database?

    If that is the case, THANK YOU!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It uses whatever the current project connection is. Which in the case of an adp, is most likely your SQL Server.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Posts
    126
    I think the DoCmd.runsql will work great, I will probably end up using that.

    However, using an ADO connection works too!

    I just replaced
    Code:
    Dim db As DAO.Database
    Set db = CurrentDb
    With
    Code:
    Dim db As ADODB.Connection
    Set db = Application.CurrentProject.Connection
    The immediate benefit of that is that all of the db.execute statements already in the code will continue to work.

    What I now have to deal with is all of the table creation statements were intended for a JET database, so I just need to rewrite them for SQL.

    But anyway...I think I'm good to go. Your suggestion was exactly what I was looking for.

    Thank you.

  6. #6
    Join Date
    Feb 2004
    Posts
    126
    Ok, so I'm good to go creating my tables and query . Thank you Teddy.

    Now...I was wondering if anyone would be able to shed any light on how to do what the below sub is trying to accomplish. Only I am using an ADP instead of an .mdb file. All that I'm REALLY looking for is a list of the forms and reports that I can then cycle through to get their controls and then their properties.

    Code:
    Private Sub AddInventory(strContainer As String)
        Dim db As DAO.Database
        Dim wrk As DAO.Workspace
        Dim rst As DAO.Recordset
        Dim con As DAO.Container
        Dim doc As DAO.Document
        
        On Error GoTo HandleErr
        
        ' You could easily modify this, using the
        ' OpenDatabase() function, to work on any database,
        ' not just the current one.
        Call SysCmd(acSysCmdSetStatus, _
         "Retrieving " & strContainer & " container information...")
        Set wrk = DBEngine.Workspaces(0)
        Set db = wrk.Databases(0)
        Set con = db.Containers(strContainer)
        Set rst = db.OpenRecordset("zstblInventory")
        
        ' Refresh the documents
        con.Documents.Refresh
        
        wrk.BeginTrans
        For Each doc In con.Documents
            If Not IsTemp(doc.Name) Then
                rst.AddNew
                    rst("Container") = strContainer
                    rst("Owner") = doc.Owner
                    rst("Name") = doc.Name
                    rst("DateCreated") = doc.DateCreated
                    rst("LastUpdated") = doc.LastUpdated
                rst.Update
                ' Make the last modified row the current row.
            End If
        Next doc
        wrk.CommitTrans
    
    ExitHere:
    
        rst.Close
        Set rst = Nothing
        Exit Sub
        
    HandleErr:
        MsgBox Err & ": " & Err.Description, , _
         "AddInventory"
        Resume ExitHere
    End Sub

  7. #7
    Join Date
    Feb 2004
    Posts
    126
    In case anyone is interested, I solved it!!!

    Code:
    Private Sub AddInventory(strContainer As String)
        Dim rst As New ADODB.Recordset
        Dim itm As Object
        
        rst.Open "dbo.zstblInventory", CurrentProject.Connection, , adLockOptimistic, adCmdTableDirect
                
        'On Error GoTo HandleErr
        
        Call SysCmd(acSysCmdSetStatus, _
            "Retrieving " & strContainer & " container information...")
            
        For Each itm In Application.CurrentProject.AllForms
            With rst
                .AddNew
                    .Fields("Container") = "Forms"
                    .Fields("Name") = itm.Name
                    .Fields("DateCreated") = Now()
                    .Fields("LastUpdated") = Now()
                    .Fields("owner") = "MJC"
                .Update
            End With
        Next itm
        
        For Each itm In Application.CurrentProject.AllReports
            With rst
                .AddNew
                    .Fields("Container") = "Reports"
                    .Fields("Name") = itm.Name
                    .Fields("DateCreated") = Now()
                    .Fields("LastUpdated") = Now()
                    .Fields("owner") = "MJC"
                .Update
            End With
        Next itm
    
    ExitHere:
    
        rst.Close
        Set rst = Nothing
        Exit Sub
        
    HandleErr:
        MsgBox Err & ": " & Err.Description, , _
         "AddInventory"
        Resume ExitHere
    End Sub

Posting Permissions

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