Microsoft Access

Code to loop through the querydefs collection in Access

2009-02-27T03:00:00Z

Recently I was migrating some tables to SQL from an old Access database. The original programmer had put spaces and other punctuation in the field names that SQL didn't allow so I changed them, but that broke Access when I linked the table back. This code will loop through the querydef collection and find all instances of a table name or field name.

 Sub custom()
    Dim strSQL As String
    Dim qry As QueryDef
    Dim db As Database
    Set db = CurrentDb
    
    For Each qry In db.QueryDefs
        strSQL = qry.SQL
        If InStr(strSQL, "[Product Master].[Product Number]") > 0 Then
            
            Debug.Print qry.Name
            
            
            'MsgBox qry.Name
            Debug.Print strSQL
            Debug.Print "======================="
        End If
        
    Next


End Sub

VBA ADODB Frequent Error with adDate or adDBdate parameter

2008-11-14T17:56:00Z

using

[code language=’vb’]

cmd.Parameters.Append cmd.CreateParameter("@dtDate", adDate, adParamInput, 0, "11/13/2008")

[/code]

it would return this error:

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Fix by using adDBTimeStamp

Stock report opening code for Access

2008-07-29T18:40:00Z

I put the following code in all my Access reports

 Option Explicit
Dim strCompanyID As String
Dim strFormName As String
Dim mlngBgColor As Long
Dim mintCount As Integer


Private Sub Report_Close()
    If isOpen(strFormName) Then
        DoCmd.Close acForm, strFormName
        DoCmd.Restore
    End If
End Sub


Private Sub Report_Open(Cancel As Integer)
    
    Dim strDatabase As String
    Dim strStartProject As String
    Dim strEndProject As String
    Dim strStartDate As String
    Dim strEndDate As String
    
    strFormName = "frmCostCatSummary"
    
    DoCmd.OpenForm strFormName, acNormal, , , , acDialog
    
    If isOpen(strFormName) Then
        DoCmd.Maximize
        
        strStartProject = Trim(Forms!frmCostCatSummary!cboStartProject)
        strEndProject = Trim(Nz(Forms!frmCostCatSummary!cboEndProject))
        strStartDate = Trim(Nz(Forms!frmCostCatSummary!txtDate1))
        strEndDate = Trim(Nz(Forms!frmCostCatSummary!txtDate2))
        
        strCompanyID = setDatabase("qryCostCatSummary", "sp_EP_CostCatSummary '" & strStartProject & "','" & strEndProject & "','" & strStartDate & "','" & strEndDate & "'")
        lblCompanyID.Caption = strCompanyID
    Else
        Cancel = True
    End If
End Sub