Re: Loading Data into a spread***...

"Ernst Guckel" <ErnstGuckel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D5853A9-799A-47D0-9614-68713EB6AFBC@xxxxxxxxxxxxxxxx
> Hello,
>
> I am at a loss and was wondering if someone could lend me a hand...
>
> I am using a ledger spread*** in excel and storing data in an MS access
> database. Using ADO I wnat to load a month at a time onto this ledger...
> The code right now loads a single line... but I need to load the whole
> month... Here's some code...
>
> Function LoadData(sDate As String, sTable As String) As Boolean
>
> Dim conADOConnection1 As New Connection
> Dim cmdGetMyData As New Command
> Dim rData As Recordset
>
> Dim strConnect As String
> Dim strSQL As String
> Dim fld As Field
> Dim i As Integer
> Dim r As Range
>
> sFile = ThisWorkbook.Path & DB_FILE
>
> '************
>
> Application.ScreenUpdating = False
> 'turning screen updating off optimizes code
> Application.EnableEvents = False
> 'turn off events
>
> '**************************************************
>
> If Not conADOConnection1.State = adStateOpen Then
>
> strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
> & "Data Source = " & sFile
> conADOConnection1.Open strConnect
>
> End If
>
> strSQL = "SELECT Weather, Info, [11 - 12pm], [12 - 6am]," & _
> "[6 - 11am], [11 - 2pm], [2 - 5pm], [5 - 8pm], [8 - 10pm]," & _
> "[10 - 11pm], [Trans Time], [Pad Time], [Credit Card]," & _
> "[Total Daily Sales], [Sales Tax], [Gift Cert], Deposit," & _
> "Count, Void, Disc, [Neg/ Free], Meals, Waste, [Labor %]," & _
> "[Pay Hours], Toy, [G Bks], [(2)], [(3)], [Partial #1]," & _
> "[Partial #2], [Partial #3], [Partial #4], Dine, [To Go]" & _
> "FROM " & sTable & " WHERE Date=#" & sDate & "#;"
>
> With cmdGetMyData
> Set .ActiveConnection = conADOConnection1
> .CommandText = strSQL
> .CommandType = adCmdText
> End With
>
> Set rData = cmdGetMyData.Execute()
>
> Names("DayName").RefersTo = DayRange(17)
>
> For Each r In Range("DayName")
>
> r.Value = rData(i)
> i = i + 1
> Next
>
>
> '**************************************************
> Application.ScreenUpdating = True
> Application.EnableEvents = True
>
> Set conADOConnection1 = Nothing
> Set cmdGetMyData = Nothing
> Set rData = Nothing
>
> End Function
>
> Function DayRange(iRow As Integer) As String
>
> Dim sName As String
>
> sName = "=C" & iRow & ":Q" & iRow
> sName = sName & ",S" & iRow & ":T" & iRow
> sName = sName & ",V" & iRow & ":AC" & iRow
> sName = sName & ",AH" & iRow & ":AO" & iRow
> sName = sName & ",AQ" & iRow & ":AR" & iRow
>
> DayRange = sName
>
> End Function
>
> Any help would be great...
>
> Ernst.
>

Have you tried changing your where clause?

"FROM " & sTable & " WHERE Date Between #" & sDate1 & "# and #" & sDate2 &
"#;"

--
============
Frank Hickman
Microsoft MVP
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.


.