[RESOLVED] VBA-Having trouble putting data in the database

[southphillyman]

I made a form and i'm using the ADOB.connection to put the info into the database. But when I click the button an error comes up saying a problem occured with Microsoft communicating with OLE server or ActiveControl.

Is there a way to just use SQL to put the info into the database from my form?
This ADO/DAO stuff is not working for me.

[kfcSmitty]

here is an ADO tutorial courtesy of si_the_geek.

It shows how to use SQL in ADO

http://www.vbforums.com/showthread.php?t=337051


HTH

[si_the_geek]

The FAQ's that kfcSmitty linked to should be useful, especially Beacon's ADO Tutorial.

If you show us the code you have already (and let us know where the error occurs) we may be able to offer more precise help.

[southphillyman]

The FAQ's that kfcSmitty linked to should be useful, especially Beacon's ADO Tutorial.

If you show us the code you have already (and let us know where the error occurs) we may be able to offer more precise help.

do you want me to post the entire code?
it's like 500 lines

[kfcSmitty]

I don't think he read your post.

si -- he was asking how to connect to the database, no actual error was received or posted

If you download this example, it should show you how to accomplish what you want.

[southphillyman]

Smitty isn't that tutorial for VB.NET tho?
What syntax differences are there if any?

[si_the_geek]

There are lots of differences with VB.Net, I'd recommend not looking at VB.Net code, but VB code instead (which is very similar to VBA).

The ADO Tutorial is a good example, and may well be enough for you to spot the code you need - i not we can help.

do you want me to post the entire code?
it's like 500 lines

Nope, just the relevant part(s). eg: the code which has the error (the whole sub if it is fairly small), and the declarations for any variables/objects you use in that code.

si -- he was asking how to connect to the database, no actual error was received or posted

From my interpretation of the first line, there is code, and it is raising an error.

[southphillyman]

this is the SQL code that has syntax problems

DoCmd.RunSQL "INSERT INTO RepairOrders(CustomerName, CustomerAddress, " & _
"CustomerCity, CustomerState, CustomerZIPCode, CarMakeModel, " & _
"CarYear, ProblemDescription, Part1Name, Part1UnitPrice, " & _
"Part1Quantity, Part1SubTotal, Part2Name, Part2UnitPrice, " & _
"Part2Quantity, Part2SubTotal, Part3Name, Part3UnitPrice, " & _
"Part3Quantity, Part3SubTotal, Part4Name, Part4UnitPrice, " & _
"Part4Quantity, Part4SubTotal, Part5Name, Part5UnitPrice, " & _
"Part5Quantity, Part5SubTotal, JobPerformed1, JobPrice1, " & _
"JobPerformed2, JobPrice2, JobPerformed3, JobPrice3, " & _
"JobPerformed4, JobPrice4, JobPerformed5, JobPrice5, " & _
"TotalParts, TotalLabor, TaxRate, TaxAmount, RepairTotal, " & _
"RepairDate, TimeReady, Recommendations)"

"VALUES( [TxtCustomerName] , [CustomerAddress] ,& _
[CustomerCity] , [CustomerState] , & _
[CustomerZIPCode] , [txtCarMakeModel] , & _
[CarYear] , [txtProblemDescription] , & _
[Part1Name] , [Part1UnitPrice] , & _
[Part1Quantity] , [Part1SubTotal] , & _
[Part2Name], [Part2UnitPrice] , & _
[Part2Quantity] , [Part2SubTotal] , & _
[Part3Name] , [Part3UnitPrice] , & _
[Part3Quantity] , [Part3SubTotal] , & _
[Part4Name] , [Part4UnitPrice] , & _
[Part4Quantity] , [Part4SubTotal] , & _
[Part5Name] , [Part5UnitPrice] , & _
[Part5Quantity] , [Part5SubTotal] , & _
[JobPerformed1] , [JobPrice1] , & _
[JobPerformed2] , [JobPrice2] , & _
[JobPerformed3] , [JobPrice3] , & _
[JobPerformed4] , [JobPrice4] , & _
[JobPerformed5] , [JobPrice5] , & _
[TotalParts] , [TotalLabor] , & _
[TaxRate] , [TaxAmount] , & _
[RepairTotal] , [RepairDate] , & _
[TimeReady] , [Recommendations] );"

[si_the_geek]

Ah yes... that is because you trying to put (what I assume are) control names into your SQL statement, rather than the values that are in the controls. (you also have a lack of quotes for the second half of that code).

When using ADO you cannot refer to controls on Access forms, as ADO has no knowledge of what is going on in Access.

See this thread from the FAQ for an explanation of how to do it.

[southphillyman]

Not sure I understand what you are talking about. So for the 2nd part of that code I should use the .text extension?
And I'm not using ADO, I'm using SQL (I may be clueless in regards to what exactly ADO is tho)
Also as far as the qoute's do I have to quote each line or each individual value?

[si_the_geek]

oops, I missed the "DoCmd.RunSQL", ignore my previous post...

The only issue is the Quotes around the second half of the code. The part from VALUES onwards needs to be in the same style as the previous code (with " at the start and end of the lines), and be on the line immediately after the earlier code (or build the SQL in a variable, and just pass that to the RunSQL method).

eg:

"TotalParts, TotalLabor, TaxRate, TaxAmount, RepairTotal, " & _
"RepairDate, TimeReady, Recommendations)" [b]& _[/b]
"VALUES( [TxtCustomerName] , [CustomerAddress] , [b]"[/b] & _
[b]"[/b][CustomerCity] , [CustomerState] , [b]"[/b] & _

[southphillyman]

Still saying I have syntax errors.

[southphillyman]

It's saying I have too many line continuations.

[Hack]

Try

Dim sSQL As String
sSQL = "INSERT INTO RepairOrders(CustomerName, CustomerAddress, " 
sSQL = sSQL & "CustomerCity, CustomerState, CustomerZIPCode, CarMakeModel, "
sSQL = sSQL & "CarYear, ProblemDescription, Part1Name, Part1UnitPrice, " 
sSQL = sSQL & "Part1Quantity, Part1SubTotal, Part2Name, Part2UnitPrice, " 
sSQL = sSQL & "Part2Quantity, Part2SubTotal, Part3Name, Part3UnitPrice, " 
sSQL = sSQL & "Part3Quantity, Part3SubTotal, Part4Name, Part4UnitPrice, " 
sSQL = sSQL & "Part4Quantity, Part4SubTotal, Part5Name, Part5UnitPrice, " 
sSQL = sSQL & "Part5Quantity, Part5SubTotal, JobPerformed1, JobPrice1, " 
sSQL = sSQL & "JobPerformed2, JobPrice2, JobPerformed3, JobPrice3, " 
sSQL = sSQL & "JobPerformed4, JobPrice4, JobPerformed5, JobPrice5, " 
sSQL = sSQL & "TotalParts, TotalLabor, TaxRate, TaxAmount, RepairTotal, " 
sSQL = sSQL & "RepairDate, TimeReady, Recommendations) ,
sSQL = sSQL & "VALUES( [TxtCustomerName] , [CustomerAddress] ,
sSQL = sSQL & [CustomerCity] , [CustomerState] , 
sSQL = sSQL & [CustomerZIPCode] , [txtCarMakeModel] , 
sSQL = sSQL & [CarYear] , [txtProblemDescription] , 
sSQL = sSQL & [Part1Name] , [Part1UnitPrice] , 
sSQL = sSQL & [Part1Quantity] , [Part1SubTotal] , 
sSQL = sSQL & [Part2Name], [Part2UnitPrice] , 
sSQL = sSQL & [Part2Quantity] , [Part2SubTotal] , 
sSQL = sSQL & [Part3Name] , [Part3UnitPrice] , 
sSQL = sSQL & [Part3Quantity] , [Part3SubTotal] , 
sSQL = sSQL & [Part4Name] , [Part4UnitPrice] , 
sSQL = sSQL & [Part4Quantity] , [Part4SubTotal] , 
sSQL = sSQL & [Part5Name] , [Part5UnitPrice] , 
sSQL = sSQL & [Part5Quantity] , [Part5SubTotal] , 
sSQL = sSQL & [JobPerformed1] , [JobPrice1] , 
sSQL = sSQL & [JobPerformed2] , [JobPrice2] , 
sSQL = sSQL & [JobPerformed3] , [JobPrice3] , 
sSQL = sSQL & [JobPerformed4] , [JobPrice4] , 
sSQL = sSQL & [JobPerformed5] , [JobPrice5] , 
sSQL = sSQL & [TotalParts] , [TotalLabor] ,
sSQL = sSQL & [TaxRate] , [TaxAmount] , 
sSQL = sSQL & [RepairTotal] , [RepairDate] , 
sSQL = sSQL &[TimeReady] , [Recommendations] "  
DoCmd.RunSQL sSQL

[si_the_geek]

...with a few more quotes in
eg:

sSQL = sSQL & "VALUES( [TxtCustomerName] , [CustomerAddress] , [u]"[/u]
sSQL = sSQL & [u]"[/u][CustomerCity] , [CustomerState] , [u]"[/u]

[kfcSmitty]

From my interpretation of the first line, there is code, and it is raising an error.

Whoa, i totally must have been thinking of another post

[Hack]

...with a few more quotes in
eg:

sSQL = sSQL & "VALUES( [TxtCustomerName] , [CustomerAddress] , [u]"[/u]
sSQL = sSQL & [u]"[/u][CustomerCity] , [CustomerState] , [u]"[/u]

I did kinda miss some quotes there didn't I?

Oh well, if he copy and pastes that into a code window, the IDE should put the ending quotes in.

[southphillyman]

Good Idea Hack. I will see if it works.
Also what does the " []" do?

[Hack]

Good Idea Hack. I will see if it works.

I never use line continuations when writing SQL. It makes it so inflexible.

Also what does the " []" do?

It is your query? Why do you have them there?

Typically brackets are using to encapsulate field names that have a space in them (and if you create field names with a space in them, you should be whipped ), and to encapsulate field names that are reserved words like Date, Time, etc (another whipping for anyone using reserved words for field names )

However, in reviewing your query, I don't see spaces or reserved words so I have no idea why they are being used.

[si_the_geek]

My assumption is that they are the names of controls on the form - and that Access will automagically replace the names with the values in the controls, and delimit them etc.