LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 19
Default Import Access records to excel (parameter is a called funct) v.20

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

--
Thanks,

Patrick
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Access records into Excel dksaluki Excel Discussion (Misc queries) 1 December 20th 07 07:08 AM
Linking parameter query from Access to pivot table in Excel ken1975 Excel Discussion (Misc queries) 2 June 20th 06 01:51 PM
import data from an Access parameter query PluckyMo Excel Discussion (Misc queries) 2 April 12th 06 04:42 AM
Import records from Access to Excel Mark Excel Discussion (Misc queries) 1 December 20th 05 01:43 PM
I can import Access Tables. But, I can't import Access queries nickg420[_8_] Excel Programming 0 August 5th 04 07:46 PM


All times are GMT +1. The time now is 03:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"