|
 |
access thread: Access Query-By-From problem with Dim db as database
Message #1 by "Aaron" <goldenrugs@a...> on Fri, 20 Sep 2002 19:00:34
|
|
Thanks for looking at this message.
I am having problems with a project I am working on that uses QBF (Query
by form) to retreive the users query request, build a SQL string and
execute. I took an example from the Wrox book Beginning Access 2000 VBA.
For the following function I copied it directly from the CD accompanying
the book. My example wont work ??? but the example in the book works fine.
When the form is executed it states: compile error. user-defined type not
defined and it highlights the Dim db as database. When I run the ice cream
example it works fine so I am not sure why it is hanging up on the dim db
as database line. Here is the code for the function, though I copied it
directly from the code in the book:
Function BuildResultsTable(sSQL As String, _
sTableName As String, _
lRecordsAffected As Long) (I also tried it
with 'as boolean' here)
Dim db As Database (<- problem area highlighted by VBA)
Dim qdfAction As QueryDef
Set db = CurrentDb
On Error Resume Next
db.TableDefs.Delete sTableName
On Error GoTo 0
sSQL = Replace(sSQL, " FROM ", " INTO " & sTableName & " FROM ")
Set qdfAction = db.CreateQueryDef("", sSQL)
qdfAction.Execute dbFailOnError
lRecordsAffected = qdfAction.RecordsAffected
qdfAction.Close
BuildResultsTable = True
End Function
If you need any additional code to evaluate the problem I will be happy to
send it to you. Thanks in advance for any guidance you can give me as I am
not an expert in programming.
Aaron
Message #2 by "Carnley, Dave" <dcarnley@a...> on Fri, 20 Sep 2002 13:06:47 -0500
|
|
try "DAO.Database" or "ADO.Database" depending on which you are using...
-----Original Message-----
From: Aaron [mailto:goldenrugs@a...]
Sent: Friday, September 20, 2002 2:01 PM
To: Access
Subject: [access] Access Query-By-From problem with Dim db as database
Thanks for looking at this message.
I am having problems with a project I am working on that uses QBF (Query
by form) to retreive the users query request, build a SQL string and
execute. I took an example from the Wrox book Beginning Access 2000 VBA.
For the following function I copied it directly from the CD accompanying
the book. My example wont work ??? but the example in the book works fine.
When the form is executed it states: compile error. user-defined type not
defined and it highlights the Dim db as database. When I run the ice cream
example it works fine so I am not sure why it is hanging up on the dim db
as database line. Here is the code for the function, though I copied it
directly from the code in the book:
Function BuildResultsTable(sSQL As String, _
sTableName As String, _
lRecordsAffected As Long) (I also tried it
with 'as boolean' here)
Dim db As Database (<- problem area highlighted by VBA)
Dim qdfAction As QueryDef
Set db = CurrentDb
On Error Resume Next
db.TableDefs.Delete sTableName
On Error GoTo 0
sSQL = Replace(sSQL, " FROM ", " INTO " & sTableName & " FROM ")
Set qdfAction = db.CreateQueryDef("", sSQL)
qdfAction.Execute dbFailOnError
lRecordsAffected = qdfAction.RecordsAffected
qdfAction.Close
BuildResultsTable = True
End Function
If you need any additional code to evaluate the problem I will be happy to
send it to you. Thanks in advance for any guidance you can give me as I am
not an expert in programming.
Aaron
Message #3 by GoldenRugs@a... on Fri, 20 Sep 2002 14:57:27 EDT
|
|
I forgot to mention that I tried to use the DAO prefix for database. That did
not work. Im really stumped on this. Seems like it should work....
Thanks for the suggestion.
Aaron
Message #4 by "Richard Lobel" <richard@a...> on Fri, 20 Sep 2002 12:04:31 -0700
|
|
Go into Tools References and check MicrosoftDAO.
Richard Lobel
President
NoClassroom.com
Live Software training
Right over the Internet
richard@n...
Tel: (xxx) xxx-xxxx
Fax: (xxx) xxx-xxxx
*****ORIGINAL MESSAGE*****
I am having problems with a project I am working on that uses QBF (Query
by form) to retreive the users query request, build a SQL string and
execute. I took an example from the Wrox book Beginning Access 2000 VBA.
For the following function I copied it directly from the CD accompanying
the book. My example wont work ??? but the example in the book works
fine.
When the form is executed it states: compile error. user-defined type
not
defined and it highlights the Dim db as database. When I run the ice
cream
example it works fine so I am not sure why it is hanging up on the dim
db
as database line. Here is the code for the function, though I copied it
directly from the code in the book:
Function BuildResultsTable(sSQL As String, _
sTableName As String, _
lRecordsAffected As Long) (I also tried it
with 'as boolean' here)
Dim db As Database (<- problem area highlighted by VBA)
Dim qdfAction As QueryDef
Set db = CurrentDb
On Error Resume Next
db.TableDefs.Delete sTableName
On Error GoTo 0
sSQL = Replace(sSQL, " FROM ", " INTO " & sTableName & " FROM ") Set
qdfAction = db.CreateQueryDef("", sSQL) qdfAction.Execute dbFailOnError
lRecordsAffected = qdfAction.RecordsAffected qdfAction.Close
BuildResultsTable = True
End Function
If you need any additional code to evaluate the problem I will be happy
to
send it to you. Thanks in advance for any guidance you can give me as I
am
not an expert in programming.
Message #5 by "Aaron" <goldenrugs@a...> on Fri, 20 Sep 2002 20:29:43
|
|
Thank you Richard. Sure did overlook that one. Should have said something
in the book....oh well. i appreciate your help. take care!
Aaron
> Go into Tools References and check MicrosoftDAO.
Richard Lobel
President
NoClassroom.com
Live Software training
Right over the Internet
richard@n...
Tel: (xxx) xxx-xxxx
Fax: (xxx) xxx-xxxx
*****ORIGINAL MESSAGE*****
I am having problems with a project I am working on that uses QBF (Query
by form) to retreive the users query request, build a SQL string and
execute. I took an example from the Wrox book Beginning Access 2000 VBA.
For the following function I copied it directly from the CD accompanying
the book. My example wont work ??? but the example in the book works
fine.
When the form is executed it states: compile error. user-defined type
not
defined and it highlights the Dim db as database. When I run the ice
cream
example it works fine so I am not sure why it is hanging up on the dim
db
as database line. Here is the code for the function, though I copied it
directly from the code in the book:
Function BuildResultsTable(sSQL As String, _
sTableName As String, _
lRecordsAffected As Long) (I also tried it
with 'as boolean' here)
Dim db As Database (<- problem area highlighted by VBA)
Dim qdfAction As QueryDef
Set db = CurrentDb
On Error Resume Next
db.TableDefs.Delete sTableName
On Error GoTo 0
sSQL = Replace(sSQL, " FROM ", " INTO " & sTableName & " FROM ") Set
qdfAction = db.CreateQueryDef("", sSQL) qdfAction.Execute dbFailOnError
lRecordsAffected = qdfAction.RecordsAffected qdfAction.Close
BuildResultsTable = True
End Function
If you need any additional code to evaluate the problem I will be happy
to
send it to you. Thanks in advance for any guidance you can give me as I
am
not an expert in programming.
|
|
|
View Cart
