Multiple Copy Access Query Data to Specific Excel Worksheets

[rmoreno]

Hello all, this is a variation of a prior post titled "Copy Access Query Data to Specific Excel Worksheet". The code developed to accomplish the Access to Excel Worksheet works great, but I would like to go one step further. I have four spreadsheets (three are hidden) that are populated from four different Access queries. How do I modify the VBA coding to update all four spreadsheets with only one module run? I have the original code listed below:

Sub GetAccessData_Test()
    Dim con As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim wks As Worksheet
    
    'open connection to database
    con.ConnectionString = "\\Data\Access2Excel_Test.mdb"
    con.Provider = "Microsoft.Jet.OLEDB.4.0"
    con.Open
    
    'open recordset
    rst.ActiveConnection = con
        
    'open target query
    rst.Open "qry_Access2Excel_TestData"
    
    'set/clear/output to target worksheet
    Set wks = Sheets("Access2Excel_Test")
    wks.Range("A5:M60000").ClearContents
    wks.Range("A5").CopyFromRecordset rst
    Range("A4").Select
    
    'clean up
    Set rst = Nothing
    con.Close
    Set con = Nothing
        
End Sub

[June7]

Just repeat the code but reference each query and spreadsheet name.

rst.Close
'open second target query
rst.Open "query2"
'set/clear/output to target worksheet
Set wks = Sheets("sheetname")
wks.Range("A5:M60000").ClearContents
wks.Range("A5").CopyFromRecordset rst
Range("A4").Select
rst.Close
'open third target query
...

[rmoreno]

Thanks. Does it matter if a worksheet is hidden with this code?

[June7]

Shouldn't. My code to populate cells on hidden sheet works.

[rmoreno]

OK Thanks. I'm going to update my code module and try it.

[rmoreno]

Ran into an interesting issue when trying to run the modified code. When it tried to run my last query I got an error regarding a Public Function I use to convert UNIX date/time stamp into a Daylight Savings Time format. Why would the code hiccup on a field within my query that uses a Public Function?

[June7]

Don't know, don't remember ever trying import/export of query that called a public function. How complex is the function? Want to post it?

Instead of calling function, maybe do calculation right in the query:

DateAdd("s", [UNIX date field], #1/1/1970 0:0:0#)

[rmoreno]

The function was created so that the converted date/time would take into account Daylight Savings Time.

Public Function UnixToStdDateTime(varUnixInput As Variant) As Variant
    Dim dteStart As Date, dteEnd As Date
    If IsNull(varUnixInput) = False Then
        
        UnixToStdDateTime = DateAdd("s", varUnixInput, #12/31/1969 7:00:00 PM#)
        
        dteStart = DLookup("BeginDST", "tbl_DST", "intYear=" & Year(UnixToStdDateTime))
        dteEnd = DLookup("EndDST", "tbl_DST", "intYear=" & Year(UnixToStdDateTime))
        
        If UnixToStdDateTime < dteStart Or UnixToStdDateTime > dteEnd Then
'           This date falls outside of daylight savings time.  Subtract an hour.
            UnixToStdDateTime = DateAdd("h", -1, UnixToStdDateTime)
        
        End If
    End If
End Function

[June7]

I tested export of query calling function with TransferSpreadsheet method from Access and the calculated dates exported without error. However, when I try to use your code behind Excel and the public function, I get 'Undefined function' error.

Could break up that function and do the calcs in query.

SELECT DateAdd("s",[UNIX],#1/1/1970#) AS ConvDate, #3/10/2013# AS BeginDST, #11/3/2013# AS EndDST, DateAdd("h",IIf([ConvDate] Between [BeginDST] And [EndDST],0,-1),[ConvDate]) AS AdjDate
FROM Table1;

Use the DLookups in place of the BeginDST and EndDST constants.

My reasearch indicates 1/1/1970 0:0:0 is the UNIX base date/time.

[rmoreno]

I like your approach. How do I take into account that I have data from 2005 thru 2013?

[June7]

Isn't that what the DLookups deal with?

[rmoreno]

Yes. But if I understand your formula you have to program in a DST start and end date. Would this same formula work for a UNIX converstion date of mm/dd/2007?

[June7]

From my post 9:

Use the DLookups in place of the BeginDST and EndDST constants.

Try:
DateAdd("h", IIf([ConvDate] BETWEEEN DLookup("BeginDST", "tbl_DST", "intYear=" & Year([ConvDate])) AND DLookup("EndDST", "tbl_DST", "intYear=" & Year([ConvDate])), 0, -1) AS AdjDate

[rmoreno]

Alright, this is the formula I entered into my query:
DateSaved: DateAdd("h", iif([ConvDate] BETWEEN Dlookup("BeginDST", "tbl_DST", "intYear=" & Year([ConvDate])) AND Dlookup("EndDST", "tbl_DST", "intYear=" & Year([ConvDate])), 0, -1))

It's giving me an error: "The expression you entered as a function containing the wrong number of arguments"

I thought I entered the formula correctly. What am I missing?

[June7]

Oops, I forgot the date value for the DateAdd:

DateAdd("h", IIf([ConvDate] BETWEEEN DLookup("BeginDST", "tbl_DST", "intYear=" & Year([ConvDate])) AND DLookup("EndDST", "tbl_DST", "intYear=" & Year([ConvDate])), 0, -1), [ConvDate])