VBA Code Create query (1 Viewer)

[Crilen007]

Creating a query with VBA Code




I am trying to have my program, check for a query, if its there, erase it, and make a new one based on an SQL string.


I tried searching for this (tried "vba create query" and "create query with code")

nothing came up related to my problem.

So here I am.

Anyhelp would be greatly appreciated as always. Thanks!

 

[Crilen007]

if ObjectExists("Query", "qryName") then
DoCmd.DeleteObject acTable, "qryName"
end if


Set querydef = object.CreateQueryDef ("qryName", "sqltext")



I guess this is it.

Finally found it.

 

[ghudson]

You are halfway there. The ObjectExists() is not a built-in function. Here it is...

' Pass the Object type: Table, Query, Form, Report, Macro, or Module
' Pass the Object Name
Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     
     Set db = CurrentDb()
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.NAME = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.NAME = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).NAME = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).NAME = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
     
End Function

 

[Crilen007]

Ah, thanks =)

 

[Crilen007]

Dim qryDef As QueryDef

Set qryDef= Object.CreateQueryDef("qryBeforePrice", rstSQL)


I am trying to create a query. I have this in my code

It tells me "object required"

Any ideas?

 

[jarico75@yahoo.]

Add New Reference

Because you are using Access 2003, the method you are using will not work unless you add the Microsoft DAO 3.6 Object Library. DAO is becoming an obsolete connection method, however it still has some bang where ADO does not.