pmcd2012
asked on
Excel 2007 VBA update Access 2007 date field
I have an access database table with a date field among about 50 other fields. When the entire record is originally created, this particular date field may or may not be entered. Subsequently, I will need to update this field with a date. When I do this from Excel VBA, for example, trying to update with 3/13/2012, the field is populated with 12/29/1899 every time. If I run the query from inside Access, the field populates correctly. My VBA code is below:
Sub UpdateCDRBPCD()
Dim DBConnection1 As New ADODB.Connection
Dim rgInput As Range
Dim rgInputCell As Range
Dim rowCounter As Long
Dim intCounter As Integer
Dim intDealno As Integer
Dim SQL1 As String
Dim EnrollFTDT As String
Dim FolderLoc As String
FolderLoc = Sheets("Conversion").Range("F4")
Sheets("BPCD").Select
DBConnection1.Provider = "Microsoft Jet 4.0 OLE DB Provider"
DBConnection1.Properties("Jet OLEDB:System database") = "" & FolderLoc & "\Security.mdw"
DBConnection1.ConnectionString = "Data Source=" & FolderLoc & "\MMCD.mdb;User ID=user1;Password=password1;"
DBConnection1.Open
rowCounter = 5
Do While Cells(rowCounter, 2) <> ""
rowCounter = rowCounter + 1
Loop
Set rgInput = Range(Cells(5, 2), Cells(rowCounter - 1, 2))
intCounter = 0
For Each rgInputCell In rgInput
If Sheets("BPCD").Range("AU5").Offset(intCounter, 0) = "No" Then
Else
EnrollFTDT = Format$(Sheets("BPCD").Range("AK5").Offset(intCounter, 0), "mm/dd/yyyy")
intDealno = Sheets("BPCD").Range("H5").Offset(intCounter, 0)
SQL1 = "UPDATE MMCD SET Enrollment_File_Transfer = #" & EnrollFTDT & "# and Update_UserID = 'updateuser' where [PPT #] = " & intDealno & ""
DBConnection1.Execute SQL1
End If
intCounter = intCounter + 1
Next rgInputCell
DBConnection1.Close
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER