How can I insert data from an Access form into a sql table using VBA?

Question

Here is what I have, I'm trying to take fields from an Access form (data comes from one linked sql table) and insert them into another linked sql table:

StrSQL = "INSERT INTO [dbo_Expense_Projection] ([StatusID],[TravellerUFID],[Email]) " & _
          VALUES(" & Me.StatusID & ", " Me.TravellerUFID & ", " Me.SubmitterUFID & ", " Me.email & ")
DoCmd.RunSQL StrSQL

But I am getting this error

Compile error: Sub or Function not defined

fthiella · Accepted Answer · 2015-09-16 18:24:41Z

3

I think you are just missing some double quotes:

StrSQL = "INSERT INTO [dbo_Expense_Projection] ([StatusID],[TravellerUFID],[Email]) " & _
"VALUES(" & Me.StatusID & ", " Me.TravellerUFID & ", " Me.SubmitterUFID & ", """ & Me.email & """)"
DoCmd.RunSQL StrSQL

You can try to print the contents of StrSQL and check the query before running it:

Debug.Print StrSQL

but I prefer not to create SQL strings with concatenated values (what happens if Me.StravellerUFID contains a double quote?)

I would suggest you to insert data using DAO:

Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset("dbo_Expense_Projection")
rs.AddNew
rs!StatusID = Me.StatusID
rs!TravellerUFID = Me.TravellerUFID
' ...other fields
rs.Update
rs.Close

edited Sep 16, 2015 at 18:24

answered Sep 16, 2015 at 15:22

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fionnuala Over a year ago

There is a lot to be said for parameters. They scale better if you need to change the back-end.

asdev · Accepted Answer · 2015-09-17 10:11:25Z

0

There are also some ampersands missing in the SQL-String:

StrSQL = "INSERT INTO [dbo_Expense_Projection] ([StatusID],[TravellerUFID],[Email]) " & _
          "VALUES(" & Me!StatusID & ", " & Me!TravellerUFID & ", " & Me!SubmitterUFID & ", """ & Me!email & """)"

And I think you should use exclamation mark between "Me" and fieldname. But I do not want to argue with the experts here about that... ;)

edited Sep 17, 2015 at 10:11

answered Sep 17, 2015 at 9:29

asdev

9436 silver badges9 bronze badges

Comments

Andy Hunn · Accepted Answer · 2015-09-17 17:59:37Z

0

Here is what ended up working:

Dim StrSQL As String

StrSQL = "INSERT INTO dbo_Expense_Projection (StatusID,TravellerUFID,Email) 
          VALUES('" & Form!StatusID & "','" & Form!TravellerUFID & "','" & Form!Email & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True

answered Sep 17, 2015 at 17:59

Andy Hunn

212 silver badges7 bronze badges

Collectives™ on Stack Overflow

How can I insert data from an Access form into a sql table using VBA?

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related