Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to refer to Access queries in VBA module? 2
- Thread starter sjh
- Start date
- Thread starter
- #3
- Thread starter
- #5
I am stuck once again.
When I try to add this reference - "Microsoft DAO 3.51 Object Library" (there is also DAO 3.6), it gives me this error.
"Name conflicts with existing module, project, or object library."
Currently, I have the following selected as references
- Visual basic for applications
- MS Access 10.0 Object library
- OLE automation
- MS ActiveX Data Objects 2.1 library
- MS DAO 2.5/3.51 Compatibility library
Thank you for your help!
When I try to add this reference - "Microsoft DAO 3.51 Object Library" (there is also DAO 3.6), it gives me this error.
"Name conflicts with existing module, project, or object library."
Currently, I have the following selected as references
- Visual basic for applications
- MS Access 10.0 Object library
- OLE automation
- MS ActiveX Data Objects 2.1 library
- MS DAO 2.5/3.51 Compatibility library
Thank you for your help!
Bullsandbears123
Technical User
If you want to change/read the SQL in a query you can use the following.
****TO CHANGE SQL IN QUERY********
Dim mysql As QueryDef
Dim db As Database
Set db = CurrentDb()
Set mysql = db.QueryDefs("your query name"
With mysql
.sql = "Your query def(SQL statement"
End With
Set mysql = Nothing
***** TO SET READ THE SQL FROM QUERY THEN SET IT TO STRING VALUE*********
Dim mysql As QueryDef
Dim db As Database
dim SQLstring as string
Set db = CurrentDb()
Set mysql = db.QueryDefs("your query name"
SQLstring=mysql.sql
Set mysql = Nothing
****TO CHANGE SQL IN QUERY********
Dim mysql As QueryDef
Dim db As Database
Set db = CurrentDb()
Set mysql = db.QueryDefs("your query name"
With mysql
.sql = "Your query def(SQL statement"
End With
Set mysql = Nothing
***** TO SET READ THE SQL FROM QUERY THEN SET IT TO STRING VALUE*********
Dim mysql As QueryDef
Dim db As Database
dim SQLstring as string
Set db = CurrentDb()
Set mysql = db.QueryDefs("your query name"
SQLstring=mysql.sql
Set mysql = Nothing
- Thread starter
- #8
Hi mpastore,
I think I was getting the error message, because I declared rs as ADODB.Recordset instead of just Recordset. How are these two different, and would you also know why ADODB was causing the error?
When I refer to my Access Queries, then is it not necessary to do rs.Open sql, CurrentProject.Connection???
Thank you!!! -sjh
I think I was getting the error message, because I declared rs as ADODB.Recordset instead of just Recordset. How are these two different, and would you also know why ADODB was causing the error?
When I refer to my Access Queries, then is it not necessary to do rs.Open sql, CurrentProject.Connection???
Thank you!!! -sjh
ADO (ActiveX data objects) is the newer way to access data, DAO (data access objects) is still popular but ADO probably is the way to go, especially if you are not set in your ways. I will eventually crack and get proficient with ADO.
Mike Pastore
Hats off to (Roy) Harper
Mike Pastore
Hats off to (Roy) Harper
A couple of ways with ADO.
'- set reference which is the ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection
Dim vn As View
Set vn = cg.Views("query1"
sql1 = "Select * from query1"
rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly
OR
rs.Open sql1, connString, adOpenForwardOnly, adLockReadOnly
'- set reference which is the ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection
Dim vn As View
Set vn = cg.Views("query1"
sql1 = "Select * from query1"
rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly
OR
rs.Open sql1, connString, adOpenForwardOnly, adLockReadOnly
ChrisN0123
IS-IT--Management
Hi,
I am trying to link the calendar to an access
database to switch fields in the database.
Eg. I click on 1st June 2003 and it will change
to the record line in the database.
The calendar does move by date/day if you move the
field in access, but doesnt do it if you click on the
relevant date on the calendar.
All I need is the code the the Calendar1_Click().
The database field is named "DayView" and the database
control is Adodc1.
Any help would be greatly appreciated!!
Regards,
Chris N
I am trying to link the calendar to an access
database to switch fields in the database.
Eg. I click on 1st June 2003 and it will change
to the record line in the database.
The calendar does move by date/day if you move the
field in access, but doesnt do it if you click on the
relevant date on the calendar.
All I need is the code the the Calendar1_Click().
The database field is named "DayView" and the database
control is Adodc1.
Any help would be greatly appreciated!!
Regards,
Chris N
-
2
- #12
sjh,
Everyone else has weighed in, so here's another way (with an ADO Recordset and Connection):
Public Function ADOStoredQry()
Dim rst As New ADODB.Recordset
Dim con As New ADODB.Connection
con.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & CurrentDb.Name & ";"
rst.Open "Query3", con, , , adCmdStoredProc
rst.MoveFirst
'Whatever you want to do with the recordset...
rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Function
Using the Currentdb.Name avoids having to hard code the location of the database, in case you want to move it to a different drive or folder later.
Use adCmdStoredProc if you want to run an existing query
Use adCmdText if you want to hard code a SQL statement
Use adCmdTable if you want to return the contents of a table
Anyway, that's the way I do it.
Good luck,
Tranman
Everyone else has weighed in, so here's another way (with an ADO Recordset and Connection):
Public Function ADOStoredQry()
Dim rst As New ADODB.Recordset
Dim con As New ADODB.Connection
con.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & CurrentDb.Name & ";"
rst.Open "Query3", con, , , adCmdStoredProc
rst.MoveFirst
'Whatever you want to do with the recordset...
rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Function
Using the Currentdb.Name avoids having to hard code the location of the database, in case you want to move it to a different drive or folder later.
Use adCmdStoredProc if you want to run an existing query
Use adCmdText if you want to hard code a SQL statement
Use adCmdTable if you want to return the contents of a table
Anyway, that's the way I do it.
Good luck,
Tranman
- Thread starter
- #13
sjh,
It's a little hard to tell what you're asking, but if what you are wanting to do is use some piece of data from the recordset as criteria in a query, here's how:
On the criteria line of the query, instead of putting a value, put the name of the function where the "ADO Query" is located, followed by a pair of parenthesis.
The SQL will look like this:
SELECT CUST.CUST_NBR, CUST.F_NAME, CUST.L_NAME
FROM CUST
WHERE CUST.CUST_NBR=myFunction();
The function is like this:
Public function myFunction() as String
<all of the Dims, open connection, open recordset; etc.>
myFunction = rst.Fields("<field name>"
End Function
Now, each time the query is run, it will fetch the criteria from the recordset first.
Of course, if you need to, you can pass arguments to the function, and use them to modify the function.
If what you were asking is how to use the "Parameter Collection" of the ADO, just let me know, and I'll send you some sample code that does that. (It is quite a bit more complicated than what I talked about above, but not so bad that you couldn't do it, if you really need to).
If you don't know for sure what technique you need to use, just write and describe what you're trying to accomplish, and I'll try to come up with a simple solution.
Thanks for the star.
Tranman
It's a little hard to tell what you're asking, but if what you are wanting to do is use some piece of data from the recordset as criteria in a query, here's how:
On the criteria line of the query, instead of putting a value, put the name of the function where the "ADO Query" is located, followed by a pair of parenthesis.
The SQL will look like this:
SELECT CUST.CUST_NBR, CUST.F_NAME, CUST.L_NAME
FROM CUST
WHERE CUST.CUST_NBR=myFunction();
The function is like this:
Public function myFunction() as String
<all of the Dims, open connection, open recordset; etc.>
myFunction = rst.Fields("<field name>"
End Function
Now, each time the query is run, it will fetch the criteria from the recordset first.
Of course, if you need to, you can pass arguments to the function, and use them to modify the function.
If what you were asking is how to use the "Parameter Collection" of the ADO, just let me know, and I'll send you some sample code that does that. (It is quite a bit more complicated than what I talked about above, but not so bad that you couldn't do it, if you really need to).
If you don't know for sure what technique you need to use, just write and describe what you're trying to accomplish, and I'll try to come up with a simple solution.
Thanks for the star.
Tranman
Hi, I am having a similar problem.
My code is:
Dim rs As Recordset
Dim SQL As String
SQL = "SELECT count(table1.test) as TestCount FROM table1"
Set rs = CurrentDb().OpenRecordset(SQL)
Me.txtTest = rs("TestCount"
rs.Close
Runtime error '13' Type Mismatch.
Tried the ADODB for Dim rs As ADODB.Recordset and still the same error.
Any other thoughts of how to resolve it?
Txs in advance
Mike
My code is:
Dim rs As Recordset
Dim SQL As String
SQL = "SELECT count(table1.test) as TestCount FROM table1"
Set rs = CurrentDb().OpenRecordset(SQL)
Me.txtTest = rs("TestCount"
rs.Close
Runtime error '13' Type Mismatch.
Tried the ADODB for Dim rs As ADODB.Recordset and still the same error.
Any other thoughts of how to resolve it?
Txs in advance
Mike
Mike,
As far as I know, (which is admittedly not very far most of the time), you cannot put the parentheses after the CurrentDB reference (I don't know why that is, because CurrentDb is a function that returns a reference to the "current" database), anyway, you would need to just do this:
Set rs = CurrentDb.OpenRecordset(SQL)
Here's a line of code from one of my modules where I do essentially the same thing (with DAO):
Set datetest = CurrentDb.OpenRecordset("Select top 1 [mydate] from tester order by mydate desc;"
Tranman
As far as I know, (which is admittedly not very far most of the time), you cannot put the parentheses after the CurrentDB reference (I don't know why that is, because CurrentDb is a function that returns a reference to the "current" database), anyway, you would need to just do this:
Set rs = CurrentDb.OpenRecordset(SQL)
Here's a line of code from one of my modules where I do essentially the same thing (with DAO):
Set datetest = CurrentDb.OpenRecordset("Select top 1 [mydate] from tester order by mydate desc;"
Tranman
- Status
Similar threads
- Locked
- Solved
- Locked
- Question
- Locked
- Question