Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2007 VBA update Access 2007 date field

Posted on 2012-03-21
2
Medium Priority
?
523 Views
Last Modified: 2012-08-13
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

Open in new window

0
Comment
Question by:pmcd2012
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 37748051
In line 40 replace the word  'and' with a comma
0
 

Author Comment

by:pmcd2012
ID: 37748081
Ok, now I feel like a noob at this.  Thanks, works like a charm.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question