?
Solved

Need help with a SQL Append Query & Access VBA

Posted on 2004-11-27
7
Medium Priority
?
878 Views
Last Modified: 2008-02-26


Can someone help me to modify this append query?

----------------------------------------------------------------------

Private Sub cmdPublish_Click()
Dim SQL As String
SQL = "INSERT INTO boats ( StockNo, [Year], Make, Model ) VALUES ('STK', '1999', 'Parker', '2320')"
DoCmd.RunSQL SQL
End Sub

----------------------------------------------------------------------

OK- my ODBC connection to MySQL works. Now I want to put real data in.

Let's say I had either a control on a form or a field in a table. For example:

tblBoats
   StockNo (text)
   Year (int)
   Make (text)
   Model (text)

Or simply controls on a form containing values, for ex:

txtControlStockNo
txtControlYear
txtControlMake
txtControlModel


Can someone provide me with an example of the correct syntax within VBA to modify my append query?
0
Comment
Question by:wmilliga
  • 3
  • 2
  • 2
7 Comments
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 12685898
try like:

Private Sub cmdPublish_Click()
Dim SQL As String

strStockNo = replace$("" & txtControlStockNo.value,"'","''")
strYear = txtControlYear.value
strMake = replace$("" & txtControlMake.value,"'","''")
strModel = replace$("" & txtControlModel.value,"'","''")

SQL = "INSERT INTO boats ( StockNo, [Year], Make, Model ) VALUES (" & strStockNo & ", " & strYear & ", '" & strMake & "', '" & strModel & "')"
DoCmd.RunSQL SQL

End Sub
0
 
LVL 54

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 12685900
oops, the statement should be as:

SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES (" & strStockNo & ", " & strYear & ", '" & strMake & "', '" & strModel & "')"
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12685942
ryancs - shouldn't it be:

SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES ('" & strStockNo & "', " & strYear & ", '" & strMake & "', '" & strModel & "')"


StockNo is declared as Text, so the '...' are needed around strStockNo.


AW
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:MikeAHogan
ID: 12686050
I'm not sure if you want to go through the trouble of setting everything to variables before adding into your query, if you dont, just use this syntax
                           
Private Sub cmdPublish_Click()
Dim SQL As String                            
SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES ('" & me!
txtControlStockNo & "', " & me!txtControlYear & ", '" & me!txtControlMake & "', '" & me!txtControlModel & "')"
DoCmd.RunSQL SQL
End Sub


By the way, you should maybe look at changing the field name "Year" to something a little different like intYear or Yr  
Year() is a vba fucntion and could cause problems down the road.
0
 
LVL 5

Expert Comment

by:MikeAHogan
ID: 12686059
Or if you're concerned about users entering ' in the text fields change the quatations to this

SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES (""" & me!
txtControlStockNo & """, " & me!txtControlYear & ", """ & me!txtControlMake & """, """ & me!txtControlModel & """)"
0
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 12686154
Arthur_Wood , you're right, my mistake there! ;-)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12686798
just wanted to avoid a follow up question from wmilliga when the SQL threw an error.   I have great confidence in you, ryancs.

AW
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

862 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