MY OLD DAO project...
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
MY OLD DAO project...
I just have an DAO connection opened via workspce(0), named DB.
I need to be sure to close all recordset opened, when i click on button1...
possible via vba for excel and access database.
I need to be sure to close all recordset opened, when i click on button1...
possible via vba for excel and access database.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MY OLD DAO project...
You could use code like this:
Code: Select all
Dim rst As DAO.Recordset
On Error Resume Next
For Each rst In DB.Recordsets
rst.Close
Next rst
On Error GoTo 0 ' or to your error handler
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: MY OLD DAO project...
HansV wrote:You could use code like this:
Code: Select all
Dim rst As DAO.Recordset On Error Resume Next For Each rst In DB.Recordsets rst.Close Next rst On Error GoTo 0 ' or to your error handler
no time to migrate to ADO..., repeat
prob to pass param into the query access...
my code for test:
Code: Select all
Option Explicit
Sub CreateRecordSet()
Dim wspDefault As DAO.Workspace
Dim DB As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset
Dim qry As DAO.QueryDef
Set wspDefault = DBEngine.Workspaces(0)
Set DB = wspDefault.OpenDatabase("C:\ASS_MF\DATABASE\BA.mdb")
Set qry = DB.QueryDefs("Query13")
qry.Parameters("FDT").Value = "01520001609"
Set rst = qry.OpenRecordset()
End Sub
SELECT RAPP.RAPPORTO FROM [RAPP].RAPPORTO WHERE RAPP.RAPPORTO=[@FDT];
my table is named RAPP and filed in question RAPPORTO
perpahs i have mixed DAO and DAO
in deffect i need to pad parameter from code to FDT.... but have error, naturally
Last edited by HansV on 19 Apr 2013, 14:39, edited 1 time in total.
Reason: to move [/code] tag
Reason: to move [/code] tag
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MY OLD DAO project...
The code that I posted was DAO...
The parameter in your SQL is @FDT, so you should use
qry.Parameters("@FDT").Value = "01520001609"
The parameter in your SQL is @FDT, so you should use
qry.Parameters("@FDT").Value = "01520001609"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: MY OLD DAO project...
NOW work great!HansV wrote:The code that I posted was DAO...
The parameter in your SQL is @FDT, so you should use
qry.Parameters("@FDT").Value = "01520001609"
But...
originally i have use this statment to open rset and return data record... and i can have always a number of rrset extract with RS1.recordcount.
now, usign tyhi squery, the code return the records but not the count!!!!
ex: return count
Set RS1 = DB.OpenRecordset(SQL, dbOpenSnapshot)
new: not return count is '0'
Set QRY = DB.QueryDefs("ESTR_DATA")
QRY.Parameters("DATA_FINE").Value = Format(Me.DAL, "MM/DD/YYYY")
Set RS1 = QRY.OpenRecordset()
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MY OLD DAO project...
That is to be expected. DAO doesn't read the entire recordset into memory immediately, so it doesn't know how many records there are. If you really need to know the recordcount before looping through the records, use code like this after opening the recordset:
' Move to the last record. This forces DAO to read the entire recordset
RS1.MoveLast
' RecordCount will now be correct
MsgBox RS1.RecordCount
' Move back to the first record
RS1.MoveFirst
' Move to the last record. This forces DAO to read the entire recordset
RS1.MoveLast
' RecordCount will now be correct
MsgBox RS1.RecordCount
' Move back to the first record
RS1.MoveFirst
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: MY OLD DAO project...
WOW!!!!!!!!!!!!!!!!!!!!HansV wrote:That is to be expected. DAO doesn't read the entire recordset into memory immediately, so it doesn't know how many records there are. If you really need to know the recordcount before looping through the records, use code like this after opening the recordset:
' Move to the last record. This forces DAO to read the entire recordset
RS1.MoveLast
' RecordCount will now be correct
MsgBox RS1.RecordCount
' Move back to the first record
RS1.MoveFirst
Resolved!
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: MY OLD DAO project...
HansV wrote:The code that I posted was DAO...
The parameter in your SQL is @FDT, so you should use
qry.Parameters("@FDT").Value = "01520001609"
Sorry if i post on old post.
prob to pass a Date param in DAO subquery:
...
Dim DATE1 As Date
DATE1 = DateDiff("D", 7, CDate(Now))
Set QRY = DB.QueryDefs("ESTRAI_1")
QRY.Parameters("FDT").Value = CDate(DATE1)
Set RS = QRY.OpenRecordset()
...
... In (SELECT DISTINCT T31.CARTA FROM T31 WHERE T31.DATA_CONT>FDT) ...
if i insert the date in the IDE of access the query return the correct number of records but if i pass param date via code the code return 0 records!!!!
peraphs io need to format the Date param in code?
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MY OLD DAO project...
DateDiff returns a number, not a date. I think you want DateAdd instead of DateDiff:
Dim DATE1 As Date
DATE1 = DateAdd("d", 7, Now)
Set QRY = DB.QueryDefs("ESTRAI_1")
QRY.Parameters("FDT").Value = DATE1
Set RS = QRY.OpenRecordset
Dim DATE1 As Date
DATE1 = DateAdd("d", 7, Now)
Set QRY = DB.QueryDefs("ESTRAI_1")
QRY.Parameters("FDT").Value = DATE1
Set RS = QRY.OpenRecordset
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: MY OLD DAO project...
OK TKS!HansV wrote:DateDiff returns a number, not a date. I think you want DateAdd instead of DateDiff:
Dim DATE1 As Date
DATE1 = DateAdd("d", 7, Now)
Set QRY = DB.QueryDefs("ESTRAI_1")
QRY.Parameters("FDT").Value = DATE1
Set RS = QRY.OpenRecordset
But i need to calculate 7 days back to the current date, example:
now 16/04/2014
i need
09/04/2014
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MY OLD DAO project...
Oh, and keep in mind that Now returns the date + time, for example 16-Apr-2014 12:33:45.
If you only want the date, use Date instead of Now:
DATE1 = DateAdd("d", -7, Date)
If you only want the date, use Date instead of Now:
DATE1 = DateAdd("d", -7, Date)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: MY OLD DAO project...
HansV wrote:You can use
DATE1 = DateAdd("d", -7, Now)
NEW CODE:
Code: Select all
DATE1 = DateAdd("D", -15, Now)
Set QRY = DB.QueryDefs("ESTRAI_1")
QRY.Parameters("[FDT]").Value = Format(DATE1, "DD/MM/YYYY")
Set RS = QRY.OpenRecordset
Debug.Print QRY.RecordsAffected
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MY OLD DAO project...
What is the data type of the FDT parameter? If if is a DateTime parameter, you shouldn't use Format because Format returns a string, not a date. Try
Dim DATE1 As Date
DATE1 = DateAdd("d", -15, Date)
Set QRY = DB.QueryDefs("ESTRAI_1")
QRY.Parameters("FDT").Value = DATE1
Set RS = QRY.OpenRecordset
Dim DATE1 As Date
DATE1 = DateAdd("d", -15, Date)
Set QRY = DB.QueryDefs("ESTRAI_1")
QRY.Parameters("FDT").Value = DATE1
Set RS = QRY.OpenRecordset
Best wishes,
Hans
Hans