March 9, 2013 at 10:54 am
VBA - I'm trying to insert a record into MS Access from Excel.... It keeps telling me that something is wrong with the INSERT INTO statement... I tried changing the 'dateReg' (date registration) variable to delimiters #, also tried with strings ", same message. dateReg is a date field in the dB, the rest fields are text. I have no problem retrieving data (Select).
Any advice will be greatly appreciated... thx
Sub InsertRecordDB()
Dim con As Connection
Dim rs As Recordset
Set con = New Connection
con.connectionstring = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;Data Source=" & _
"C:\ExcelApplications\Databases\Development.mdb"
con.Open
' Instantiate the Recordset object, then set its properties.
Set rs = New Recordset
With rs
.Source = "INSERT INTO tblUsers values('" & txtUN.Text & "', '" & txtPW.Text & "', '" & cboUsers.Text & "', '" & txtPicNum.Text & "', '" & dateReg & "', '" & cboFloors.Text & "', '" & cboGender.Text & "', '" & cboAdmin.Text & "')"
also tried this, adding # delimiter to dateReg:
.Source = "INSERT INTO tblUsers (Username, Password, Name_Lname, Image_Number, RegisteredDate, Floor_Num, Gender, Emp_Type) values('" & txtUN.Text & "', '" & txtPW.Text & "', '" & cboUsers.Text & "', '" & txtPicNum.Text & "', #" & dateReg & "#, '" & cboFloors.Text & "', '" & cboGender.Text & "', '" & cboAdmin.Text & "')"
Set .ActiveConnection = con
.CursorLocation = adUseClient
.CursorType = adOpenStatic
' Open the recordset.
.Open
End With
Dim RowCnt, FieldCnt As Integer
RowCnt = 1
' Use field names as headers in the first row.
' For FieldCnt = 0 To rs.Fields.count - 1
' Cells(RowCnt, FieldCnt + 1).Value = _
' rs.Fields(FieldCnt).name
' Rows(1).Font.Bold = True
' Next FieldCnt
' Fill rows with records, starting at row 2.
RowCnt = 2
' While Not rs.EOF
' For FieldCnt = 0 To rs.Fields.count - 1
' Cells(RowCnt, FieldCnt + 1).Value = _
' rs.Fields(FieldCnt).Value
' Next FieldCnt
' rs.MoveNext
' RowCnt = RowCnt + 1
' Wend
con.Close
Call Sheet1.UndoSplashScreen
MsgBox "Record inserted successfully!"
End Sub
March 9, 2013 at 11:33 am
I think you need to convert the date to a string like this:
... , CAST(' " & dateReg & " ' AS VARCHAR(20)), ...
Also, if any of the values are NULL, the INSERT INTO string will be null. You should make sure all the variables being passed in are checked for null either in your VB app or by adding something like this for each column:
... , ISNULL(' " & txtPW.Text & " ',''), ...
(the last part before the paranthesis is two single quotes to specify blank as a default)
March 9, 2013 at 11:34 am
This might not be the best place for your question - this is an SQL-Server site.
Have you tried pushing your dates-as-string into #mm/dd/yyyy# format?
Mock-ISO formats also work: #yyyy-mm-dd# or #yyyy-mm-dd hh:nn:ss#
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply