MY OLD DAO project...

[sal21]

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.

[HansV]

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

[sal21]

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

my query13 into acces database:

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

[HansV]

The code that I posted was DAO...

The parameter in your SQL is @FDT, so you should use

qry.Parameters("@FDT").Value = "01520001609"

[sal21]

The code that I posted was DAO...

The parameter in your SQL is @FDT, so you should use

qry.Parameters("@FDT").Value = "01520001609"

NOW work great!

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()

[HansV]

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

[sal21]

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

WOW!!!!!!!!!!!!!!!!!!!!
Resolved!

[sal21]

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?

[HansV]

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

[sal21]

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

OK TKS!

But i need to calculate 7 days back to the current date, example:
now 16/04/2014
i need
09/04/2014

[HansV]

You can use

DATE1 = DateAdd("d", -7, Now)

[HansV]

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)

[sal21]

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

RecordsAffected=0 (!!!!!!!!!!!!!!!!!!!!)

[HansV]

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