2 Part, Excel update of Access using do until and matching dane and time formats

[TDC21]

Hello Forum,

I have a two part question, I have a macro that is attempting to update an Access table from Excel. The part I have seems to be working, that is the information is being fed into the Access table, but I get a debuging error every time it runs. The error states I must enter a value in the 'Schedule.LOCATION' field. Is there a stop loop statement I am missing?

I have four vields commented out in my code, two are short date in Execl and the others are time formated as "7:00 AM". I have had zero luck matching the date types between Excel and my access table. The formating in Excel is the required formating, any sugestions on how I can get these values to match?

I am pretty new to all of this so any help will be greatly appreciated.

Application.ScreenUpdating = False


Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset


Set cn = New ADODB.Connection


Dim strcon As String
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source=C:\Users\tcregar\Desktop\ScheduleTest 2.0\ScheduleDB.accdb;"


cn.Open (strcon)


Dim qry As String
qry = "SELECT * FROM Schedule"
rs.Open qry, cn, adOpenStatic, adLockOptimistic, adOpenKeyset


Dim rLine As Integer
rLine = 3


Dim Ref As String
Ref = Range("E" & rLine).Value


Do
 rs.AddNew
 rs.Fields("LOCATION") = Range("E" & rLine).Value
 rs.Fields("SHIFT") = Range("F" & rLine).Value
 rs.Fields("DEPARTMENT") = Range("G" & rLine).Value
 rs.Fields("SUPERVISOR") = Range("H" & rLine).Value
 rs.Fields("POSITION") = Range("I" & rLine).Value
' rs.Fields("SCHEDULE DATE") = Range("J" & rLine).Value
' rs.Fields("SCHEDULE IN") = Range("K" & rLine).Value
' rs.Fields("SCHEDULE OUT") = Range("L" & rLine).Value
' rs.Fields("WEEK ENDING DATE") = Range("M" & rLine).Value
 
 rLine = rLine + 1
 
 Loop Until Ref = ""

End Sub

 

[TDC21]

Re: 2 Part, Execl update of Access using do until and matching dane and time formats

Figured out part 1, changed Ref from string to range.

Any help on the date formating will still be greatly appreciated.

Dim Ref As Range
Set Ref = Sheets("Cover Sheet").Range("E" & rLine)


Do
 rs.AddNew
 rs.Fields("LOCATION") = Range("E" & rLine).Value
 rs.Fields("SHIFT") = Range("F" & rLine).Value
 rs.Fields("DEPARTMENT") = Range("G" & rLine).Value
 rs.Fields("SUPERVISOR") = Range("H" & rLine).Value
 rs.Fields("POSITION") = Range("I" & rLine).Value
' rs.Fields("SCHEDULE DATE") = Range("J" & rLine).Value
' rs.Fields("SCHEDULE IN") = Range("K" & rLine).Value
' rs.Fields("SCHEDULE OUT") = Range("L" & rLine).Value
' rs.Fields("WEEK ENDING DATE") = Range("M" & rLine).Value
 
 rLine = rLine + 1
 
 'Loop Until Ref = ""
 Loop Until Not IsEmpty(Ref)