Geek Answers Handbook

How to insert values ​​into a database table using VBA in MS access

I started using access recently. I am trying to insert multiple rows into a database; however, I am stuck as it throws an error that indicates Too options. I have a table test with one column in it called start_date. I want to insert all dates between two dates, for example, if I review 1/7/2014 to 3/7/2014. I need dates like 1/7 / 2014,2 / 7 / 2014,3 / 7/2014 in my table, but I have a problem inserting the code that I used looks like this

Private Sub createRec_Click()
Dim StrSQL As String
Dim InDate As Date
Dim DatDiff As Integer
Dim db As database
InDate=Me.FromDateTxt
'here I have used a code to find out the difference between two dates that i've not written
For i = 1 To DatDiff
StrSQL = "INSERT INTO Test (Start_Date) VALUES ('" & InDate & "' );"
StrSQL = StrSQL & "SELECT 'Test'"
db.Execute StrSQL
db.close
i=i+1
next i
End Sub

My code throws an error in the Db.Execuite StrSQL line as too few parameters. Hope someone can help me on this. thanks in advance

+4
3

, , ( SQL). for .

, , , . , . . for

Dim StrSQL As String
Dim InDate As Date
Dim DatDiff As Integer

InDate = Me.FromDateTxt

StrSQL = "INSERT INTO Test (Start_Date) VALUES ('" & InDate & "' );"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
+8

SQL , .

"" .

db - , : (, set db = currentdb)

VBA 32767 - Long.

, , :

INSERT INTO Test (Start_Date) VALUES ('#" & format(InDate, "mm/dd/yyyy") & "#' );"
+1
  • : = 1 DatDiff. For NEXT
  • : StrSQL = StrSQL "SELECT" Test ", Access SQL, ; INSERT INTO Test (Start_Date) VALUES ('" InDate "'); SELECT 'Test' SQL ( , , ). , SELECT

in summary: delete these two lines of code above and your insert statement will work fine. You can change the code later to suit your specific needs. And by the way, several times you have to apply dates in pounds, for example

0
source

More articles:


All Articles