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

grrr223 · #1 (**permalink**) 06-04-04, 12:39

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!

Teddy · #2 (**permalink**) 06-04-04, 12:49

I dunno if you are really dead set on using that particular method, but if your inclined to other options, DoCmd****nSQL would get the job done fairly cleanly.

grrr223 · #3 (**permalink**) 06-04-04, 12:56

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****nSQL, 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!

Teddy · #4 (**permalink**) 06-04-04, 13:02

It uses whatever the current project connection is. Which in the case of an adp, is most likely your SQL Server.

grrr223 · #5 (**permalink**) 06-04-04, 14:02

I think the DoCmd****nsql 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.

grrr223 · #6 (**permalink**) 06-04-04, 15:43

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

grrr223 · #7 (**permalink**) 06-04-04, 17:43

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

Internet Services

Web Development

Digital Marketing

Consumer Tech