Import Access records to excel (parameter is a called funct) v.20

[PSKelligan]

Hi All,

I am trying to retrieve records from 2 queries in an Access 2003 database to
Excel. The first one works fine but the 2nd is a parameter query and the
parameter is answered by a stored function. It defines a reporting period
start date based on the system clock. The function works in both access and
excel but when the query is run in the follwing code it returns an error
state the function is "undefined. Can anyone tell me how to get the import
proceedure to see and use the parameter function. I can store the date
function in access or excel. Whichever is more efficient. My code is as
follows.

Public Sub ImportFMC_MC_Data()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim objField As ADODB.Field
Dim rsData1 As ADODB.Recordset
Dim rsData2 As ADODB.Recordset
Dim Param1 As ADODB.Parameter
Dim Cmd1 As ADODB.Command
Dim lOffset As Long
Dim szConnect As String

'Trap any error/exception
'On Error Resume Next

'Body of proceedure.
'Creates and Names 2 Worksheets in the active Workbook.
ActiveWorkbook.Sheets.Add Type:=xlWorksheet, Count:=2, after:=Sheets(1)
Sheets(2).Name = "FMC Data 4-8 Days"
Sheets(3).Name = "MC Status"

'Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ERDLogTrack\LogTracker\ERD Logistics
Tracker_be.mdb;"

'Create Command Object.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = szConnect


'Create the 1st Recordset object and run the query.
Set rsData1 = New ADODB.Recordset
rsData1.Open "[qryFMC_Equipment]", szConnect, adOpenForwardOnly,
adLockReadOnly, adCmdTable

'Make sure we got records back.
If Not rsData1.EOF Then
'Add headers to the worksheet.
With Sheets(2).Range("A1")
For Each objField In rsData1.Fields
.Offset(0, lOffset).Value = objField.Name
lOffset = lOffset + 1
Next objField
.Resize(1, rsData1.Fields.Count).Font.Bold = True
End With
'Dump the contents of the recordset into the worksheet.
Sheets(2).Range("A2").CopyFromRecordset rsData1
'Fit the column widths to the data
Sheets(2).UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No Records Returned From qryFMC_Equipment.",
vbCritical, "ERD GMB"
End If

'Close the 1st recordset
rsData1.Close
Set rsData1 = Nothing

'Create the 2nd Recordset object and run the query.
Set rsData2 = New ADODB.Recordset
rsData2.Open "[qryMC_Status]", szConnect, adOpenForwardOnly,
adLockReadOnly, adCmdTable

'Make sure we got records back.
If Not rsData2.EOF Then
'Add headers to the worksheet.
With Sheets(3).Range("A1")
For Each objField In rsData2.Fields
.Offset(0, lOffset).Value = objField.Name
lOffset = lOffset + 1
Next objField
.Resize(1, rsData2.Fields.Count).Font.Bold = True
End With
'Dump the contents of the recordset into the worksheet.
Sheets(3).Range("A2").CopyFromRecordset rsData2
'Fit the column widths to the data
Sheets(3).UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No Records Returned From qryMC_Status.", vbCritical,
"ERD GMB"
End If

'Close the recordset
rsData2.Close
Set rsData2 = Nothing

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Myrna Larson]

I haven't had a lot of experience with ADO (just fumbling through it the last
few months), but are you trying to use a stored procedure that takes a
parameter?

The ADODB.Command object has a Parameters property that you set before opening
the record set. Here's some code that I use to retrieve stock quotes for a
particular date. The query is defined in the database, and it requires a
parameter (the date).

Function PricesForSpecifiedDate(DBName As String, TheDate As Date) As Variant
'uses parameter query stored in the database
Dim Cmd As ADODB.Command
Dim Param As ADODB.Parameter

Set Cmd = New ADODB.Command
Set Param = New ADODB.Parameter

With Cmd
.CommandText = "Prices_as_of"
.CommandType = adCmdStoredProc
Set Param = .CreateParameter("Target_Date", adDBDate, adParamInput)
Param.Value = TheDate
.Parameters.Append Param
End With
PricesForSpecifiedDate = GetAccessDataFromQuery(Cmd, DBName)
End Function

Function GetAccessDataFromQuery(Cmd As ADODB.Command, _
DBName As String) As Variant

Dim Cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim v As Variant

ReDim v(0, 0)

Set Cnxn = OpenConnection(DBName)
Cmd.ActiveConnection = Cnxn

Set rs = New ADODB.Recordset
With rs
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open Cmd

If .RecordCount > 0 Then
v = .GetRows
Else
v(0, 0) = "No Records"
End If
.Close
End With

Cnxn.Close
Set Cnxn = Nothing

GetAccessDataFromQuery = v
End Function 'GetAccessDataFromQuery

Function OpenConnection(dBaseName As String) As ADODB.Connection
Dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection

With Cnxn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & XLDocDir & dBaseName
.Open
End With

Set OpenConnection = Cnxn
Set Cnxn = Nothing
End Function

Hi All,

I am trying to retrieve records from 2 queries in an Access 2003 database to
Excel. The first one works fine but the 2nd is a parameter query and the
parameter is answered by a stored function. It defines a reporting period
start date based on the system clock. The function works in both access and
excel but when the query is run in the follwing code it returns an error
state the function is "undefined. Can anyone tell me how to get the import
proceedure to see and use the parameter function. I can store the date
function in access or excel. Whichever is more efficient. My code is as
follows.

Public Sub ImportFMC_MC_Data()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim objField As ADODB.Field
Dim rsData1 As ADODB.Recordset
Dim rsData2 As ADODB.Recordset
Dim Param1 As ADODB.Parameter
Dim Cmd1 As ADODB.Command
Dim lOffset As Long
Dim szConnect As String

'Trap any error/exception
'On Error Resume Next

'Body of proceedure.
'Creates and Names 2 Worksheets in the active Workbook.
ActiveWorkbook.Sheets.Add Type:=xlWorksheet, Count:=2, after:=Sheets(1)
Sheets(2).Name = "FMC Data 4-8 Days"
Sheets(3).Name = "MC Status"

'Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ERDLogTrack\LogTracker\ERD Logistics
Tracker_be.mdb;"

'Create Command Object.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = szConnect


'Create the 1st Recordset object and run the query.
Set rsData1 = New ADODB.Recordset
rsData1.Open "[qryFMC_Equipment]", szConnect, adOpenForwardOnly,
adLockReadOnly, adCmdTable

'Make sure we got records back.
If Not rsData1.EOF Then
'Add headers to the worksheet.
With Sheets(2).Range("A1")
For Each objField In rsData1.Fields
.Offset(0, lOffset).Value = objField.Name
lOffset = lOffset + 1
Next objField
.Resize(1, rsData1.Fields.Count).Font.Bold = True
End With
'Dump the contents of the recordset into the worksheet.
Sheets(2).Range("A2").CopyFromRecordset rsData1
'Fit the column widths to the data
Sheets(2).UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No Records Returned From qryFMC_Equipment.",
vbCritical, "ERD GMB"
End If

'Close the 1st recordset
rsData1.Close
Set rsData1 = Nothing

'Create the 2nd Recordset object and run the query.
Set rsData2 = New ADODB.Recordset
rsData2.Open "[qryMC_Status]", szConnect, adOpenForwardOnly,
adLockReadOnly, adCmdTable

'Make sure we got records back.
If Not rsData2.EOF Then
'Add headers to the worksheet.
With Sheets(3).Range("A1")
For Each objField In rsData2.Fields
.Offset(0, lOffset).Value = objField.Name
lOffset = lOffset + 1
Next objField
.Resize(1, rsData2.Fields.Count).Font.Bold = True
End With
'Dump the contents of the recordset into the worksheet.
Sheets(3).Range("A2").CopyFromRecordset rsData2
'Fit the column widths to the data
Sheets(3).UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No Records Returned From qryMC_Status.", vbCritical,
"ERD GMB"
End If

'Close the recordset
rsData2.Close
Set rsData2 = Nothing

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Jamie Collins]

I am trying to retrieve records from 2 queries in an Access 2003 database to
Excel. The first one works fine but the 2nd is a parameter query and the
parameter is answered by a stored function. It defines a reporting period
start date based on the system clock. The function works in both access and
excel but when the query is run in the follwing code it returns an error
state the function is "undefined.

You need to understand that your .mdb file is a Jet database. MS
Access is not a database; rather it is an application development
environment. Certain settings and elements used by the MS Access app
are stored in Jet tables in the .mdb file e.g. forms, reports, UDFs,
etc but are not otherwise visible. Generally, the only elements
visible from outside the MS Access UI are the schema (tables, views,
stored procedures, etc), the data in the tables and security elements
(users, groups, etc).

The only functions that Jet can 'see' are the ones built in, being the
VBA5 functions (as distinct from methods). Jet has no knowledge of the
user defined functions in the .mdb nor any functions provided by the
MS Access UI, including those in the MS Access library (e.g. NZ,
EuroConvert, etc) and the VBA6 functions in Access2000 and above (e.g.
Replace and StrReverse, etc). Similarly, Jet cannot see Excel library
functions nor the UDF functions in an Excel workbook.

What does your function do? Post the code and someone may be able to
suggest a way of achieving the same with sql. You could then wrap in a
stored procedure and call the proc with parameters from Excel, using
code as posted by Myrna. Actually, the more efficient way may be to
call using e.g.

.Open("EXECUTE Prices_as_of '" & _
Format$(TheDate, "yyyy-mm-dd hh:mm:ss") & "';")

Jamie.

--

 

[PSKelligan]

Thanks Jamie and Myrna for your responses.

I did not understand that about Jet -vs- Access. Very enlightening.

My function which follows is designed to generate a reporting period the
start date is on the 16th of each month and it ends on the 15th of the
follwing month. So... on each day I run the query, it will show the period
just past. For example if I run the query on August 12th 2004, the RepPeriod
wil be 16-June-2004 to 15-July-2004 but on Aug 16th the period will advance 1
month to 16-Jul-2004 to 15-August-2004. If there is a way to accomplish this
I would be very greatful for the help. Se function below:

Public Function ReportPeriod() As Variant

'Instantiate (define) all variables
Dim cDay As Integer 'Current day (sys clock).
Dim cMonth As Integer 'Current month (sys clock).
Dim cYear As Integer 'Current year (sys clock).
Dim sDay As Integer 'Reporting period start day of month
(literal).
Dim sMonth As Integer 'Reporting period start month (variant).
Dim eDay As Integer 'Reporting period end day of month (literal).
Dim eMonth As Integer 'Reporting period end month (variant).
Dim sDate, eDate As Date 'Start and end of current reporting period
(variant).
'Initialize all variables
cDay = Format(Date, "dd") 'Sets day of the current date to an
interger.
cMonth = Format(Date, "mm") 'Sets month of the current date to an
interger.
cYear = Format(Date, "yyyy") 'Sets year of the current date to an
interger.
sDay = "16" 'Day of month that reporting period
starts.
eDay = "15" 'Day of month that reporting period ends.
'Generate start date based on the system clock.
If cDay < sDay Then
sMonth = (cMonth - 2)
Else
sMonth = (cMonth - 1)
End If
sDate = (sMonth & "/" & sDay & "/" & cYear)
'Generate end date based on system clock.
If cDay < sDay Then
eMonth = (cMonth - 1)
Else
eMonth = (cMonth)
End If
eDate = (eMonth & "/" & eDay & "/" & cYear)
End Function

 

[PSKelligan]

Also Myrna,
I am working on trying to implement your example. I am definatly way newer
to ADO than you are....

Thanks,

 

[Myrna Larson]

Hi, Jamie:

Thanks for your comments, particularly your suggestion re a more efficient
method for my particular case. I'll try it. That's for the RS.Open statement,
right?

(FWIW, it took me 2-3 days of digging into books and the MS Knowledge Base to
get the syntax for executing a procedure with parameters!)

Maybe you can answer this question about parameter placeholders that stems
from article 200190 in the MSKB:

"To use a SQL statement with question marks as parameter placeholders, use the
same sample code but update the CommandText and CommandType properties as in
the following example:

objCmd.CommandText = "SELECT * FROM Products WHERE ProductID = ?"
objCmd.CommandType = adCmdText
"

The preceding code adds the parameter and sets its value this way:

Set objParam = objCmd.CreateParameter("@productid", _
adInteger, adParamInput, 0, 0)
objCmd.Parameters.Append objParam
objCmd.Parameters("@productid") = 15

My question involves a query with more than one parameter. Are the parameter
values assigned to the question mark placeholders in the order that you added
them to the parameters collection? Or can't you use the question mark when
there's more than one parameter?

Myrna Larson

 

[Myrna Larson]

I assume you need to return the variables sDate and eDate to the calling
procedure in order to set the values of parameters to the Command object.
Since you need 2 return values, I would make this a SUB, not a FUNCTION:

Note that, since sDate and eDate are Dim'd as Date variables, I use
DateSerial function to assign their values, rather than creating text and
forcing VBA to convert that text to a date.

Sub ReportPeriod(sDate As Date, eDate As Date)
Dim y As Long
Dim m As Long

y = Year(Date)
m = Month(Date) - 1 'previous month
If Day(Date) < 16 Then m = m - 1 'subtract another month

sDate = DateSerial(y, m, 16)
eDate = DateSerial(y, m + 1, 15)

End Sub

 

[Jamie Collins]

Public Function ReportPeriod() As Variant

'Instantiate (define) all variables
Dim cDay As Integer 'Current day (sys clock).
Dim cMonth As Integer 'Current month (sys clock).
Dim cYear As Integer 'Current year (sys clock).
Dim sDay As Integer 'Reporting period start day of month
(literal).
Dim sMonth As Integer 'Reporting period start month (variant).
Dim eDay As Integer 'Reporting period end day of month (literal).
Dim eMonth As Integer 'Reporting period end month (variant).
Dim sDate, eDate As Date 'Start and end of current reporting period
(variant).
'Initialize all variables
cDay = Format(Date, "dd") 'Sets day of the current date to an
interger.
cMonth = Format(Date, "mm") 'Sets month of the current date to an
interger.
cYear = Format(Date, "yyyy") 'Sets year of the current date to an
interger.
sDay = "16" 'Day of month that reporting period
starts.
eDay = "15" 'Day of month that reporting period ends.
'Generate start date based on the system clock.
If cDay < sDay Then
sMonth = (cMonth - 2)
Else
sMonth = (cMonth - 1)
End If
sDate = (sMonth & "/" & sDay & "/" & cYear)
'Generate end date based on system clock.
If cDay < sDay Then
eMonth = (cMonth - 1)
Else
eMonth = (cMonth)
End If
eDate = (eMonth & "/" & eDay & "/" & cYear)
End Function

I think you must have snipped something because your function as
posted has no return value.

Jamie.

--

 

[Jamie Collins]

Public Function ReportPeriod() As Variant

'Instantiate (define) all variables
Dim cDay As Integer 'Current day (sys clock).
Dim cMonth As Integer 'Current month (sys clock).
Dim cYear As Integer 'Current year (sys clock).
Dim sDay As Integer 'Reporting period start day of month
(literal).
Dim sMonth As Integer 'Reporting period start month (variant).
Dim eDay As Integer 'Reporting period end day of month (literal).
Dim eMonth As Integer 'Reporting period end month (variant).
Dim sDate, eDate As Date 'Start and end of current reporting period
(variant).
'Initialize all variables
cDay = Format(Date, "dd") 'Sets day of the current date to an
interger.
cMonth = Format(Date, "mm") 'Sets month of the current date to an
interger.
cYear = Format(Date, "yyyy") 'Sets year of the current date to an
interger.
sDay = "16" 'Day of month that reporting period
starts.
eDay = "15" 'Day of month that reporting period ends.
'Generate start date based on the system clock.
If cDay < sDay Then
sMonth = (cMonth - 2)
Else
sMonth = (cMonth - 1)
End If
sDate = (sMonth & "/" & sDay & "/" & cYear)
'Generate end date based on system clock.
If cDay < sDay Then
eMonth = (cMonth - 1)
Else
eMonth = (cMonth)
End If
eDate = (eMonth & "/" & eDay & "/" & cYear)
End Function

I think you must have snipped something because you function as posted
has no return value.

However, I guess you require the following sql:


SELECT
DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,2,1),16) AS
start_date,
DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,1,0),15) AS
end_date
;

Jamie.

--

 

[Jamie Collins]

Thanks for your comments, particularly your suggestion re a more efficient
method for my particular case. I'll try it. That's for the RS.Open statement,
right?

Yes, you can use EXECUTE syntax in RS.Open's Source argument

FWIW, it took me 2-3 days of digging into books and the MS Knowledge Base to
get the syntax for executing a procedure with parameters!

I agree the Command/Parameters syntax is not particularly intuitive
which is probably one reason I avoid using it, but my main reason is
that I can't help but wonder if all the jumping through hoops merely
results in an EXECUTE statement being passed to the provider anyhow,
so I may as well write that EXECUTE statement myself.

Are the parameter
values assigned to the question mark placeholders in the order that you added
them to the parameters collection? Or can't you use the question mark when
there's more than one parameter?

I don't know the answer to that one because I don't use question mark
placeholders. What is the advantage over using named arguments? e.g.

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date
;

Jamie.

--

 

[PSKelligan]

Myrna and Jamie,
Wow! I have obviously gotten in way out of my depth.

SELECT
DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,2,1),16) AS
start_date,
DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,1,0),15) AS
end_date
;

What would the syntax of the .open meathod be? Would it work to make the a
subquery that I should put in the stored query? This way I would just call
the stored query and it should run just like the other 'Non-Parameter'
queries I call.

Thanks so much for the help,

 

[PSKelligan]

Hello again,

I tried to add your sql to my query and it seemed to work ok except for the
-IIF portion of the statement. Here is my statement:

SELECT *
FROM tblHistory
WHERE (((tblHistory.date_Updated) Between
DateSerial(Year(Now()),Month(Now()-IIf(Day(Now())<16,-2,-1)),16) And
DateSerial(Year(Now()),Month(Now())-IIf(Day(Now())<16,1,0),15)))
ORDER BY tblHistory.date_Updated DESC;

Any syntax isues that are preventing this? Your little bit of sql, if it
will work in this statement will make all my problems go away.

Thanks,

Patrick

 

[Myrna Larson]

If it is "almost" working, then it looks to me as if you've introduced a bug
in Jamie's code. For the first Iff function, since it's -Iff (rather than
+Iff) the 2 and 1 inside the parentheses should be positive, not negative. If
you make them negative you are adding 2 or 1 month instead of subtracting.

FWIW, When I was doing queries by passing the SQL string, I had to pass dates
in this format: #mm/dd/yyyy#, i.e. #09/27/2004#, with the pound signs. Passing
a date variable as such didn't work for me.

So my code (in part) looked like this:

Dim SQL As String

SQL = "SELECT PrTicker, PrNAV FROM Prices WHERE PrDate = DDD" & _
" ORDER BY PrTicker"
SQL = Replace(S, "DDD", SQLDate(TheDate))

The function to format the date correctly, SQLDate, looked like this:

Function SQLDate(ADate As Date) As String
SQLDate = Format$(ADate, "\#mm/dd/yyyy\#")
End Function

I like to write out the SQL statement using "place holders" for the variables,
then use the Replace function to insert the correct values. I would write your
code as

Dim sDate As String
Dim eDate AS String
Dim SQL As String

sDate = SQLDate(DateSerial(Year(Now()), _
Month(Now()-IIf(Day(Now())<16,2,1),16))
eDate = SQLDate(DateSerial(Year(Now()), _
Month(Now()-IIf(Day(Now())<16,1,0),15))

SQL = "SELECT * FROM tblHistory WHERE (tblHistory.Date_Updated) Between
SDATE and EDATE ORDER BY tblHistory.date_Updated DESC;"
SQL = Replace(SQL,"SDATE",sDate)
SQL = Replace(SQL,"EDATE",eDate)

Then open the record set with the SQL statement.

 

[Myrna Larson]

I don't know the answer to your question as to "Why". I think that using the
question marks, particularly if there's more than one parameter, obfuscates
the code.

 

[PSKelligan]

Myrna,
Yes, the -2, -1 actually were just my attemts at troubleshooting the code.
It did not work with or without it. It querys alright but does not roll back
to previous months. tripple checked my parenthasies but no clue as to why it
isnot worki


Thanks,

Patrick

 

[Jamie Collins]

I don't know the answer to your question as to "Why". I think that using the
question marks, particularly if there's more than one parameter, obfuscates
the code.

A procedure with arguments that make it difficult and unpredictable
for a client app to use? I don't think that's one of the goals of
obfuscation <g>.

Jamie.

--

 

[Jamie Collins]

I tried to add your sql to my query and it seemed to work ok except for the
-IIF portion of the statement. Any syntax isues that are preventing this?
Your little bit of sql, if it
will work in this statement will make all my problems go away.

I think you have a paren in the wrong place and some unwanted minus signs. Try:

SELECT
*
FROM
tblHistory
WHERE
date_Updated
Between
DateSerial(
Year(Now()),
Month(Now())-IIf(Day(Now())<16,2,1),
16)
And
DateSerial(
Year(Now()),
Month(Now())-IIf(Day(Now())<16,1,0),
15)
ORDER BY
tblHistory.date_Updated DESC
;

Jamie.

--

 

[Jamie Collins]

FWIW, When I was doing queries by passing the SQL string, I had to pass dates
in this format: #mm/dd/yyyy#, i.e. #09/27/2004#, with the pound signs. Passing
a date variable as such didn't work for me.

The problem with that format is it is treated inconsistently by Jet
e.g. it sees #27/09/2004# as being the same as #09/27/2004# but
#01/04/2004# and #04/01/2004# as being different. Using an unambiguous
format (e.g. dd mmm yyyy) seems to be the solution but using a word to
represent the month could introduce in language problems.

I've come to the conclusion that the best date format for Jet is
'dddd-mm-dd' being the international standard (ISO-8601). In this
format, Jet handles ambiguous dates consistently e.g. 2004-04-01 is
always 1st April 2004 and 2004-24-01 is always rejected rather than
being coerced to 24th January 2004.

For general advantages of this date format see:

http://www.cl.cam.ac.uk/~mgk25/iso-time.html

I like to write out the SQL statement using "place holders" for the variables,
then use the Replace function to insert the correct values.

It's worth stressing what a fine idea that is.

Jamie.

--

 

[Jamie Collins]

tripple checked my parenthasies

Just to clarify, than than your

Month(Now() - <<the IFF stuff>> )

you instead require

Month(Now()) - <<the IFF stuff>>

Jamie.

--