Sql code in access form in adp

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger - 17 Feb 2006 02:10 GMT

Whats wrong with this Private Sub Save_Record_Click()
Dim SQL As String

SQL = "INSERT INTO log ([Admin],[Issue])" _
& "select [adm_name],[Problem]" _
& "from forms!frm_tick_entry"

DoCmd.RunSQL SQL
End Sub

Access2003 - sql200 back end

I am trying to create a code which will copy information from a form to a
table.
table = log
Form = frm_tick_entry
I keep getting invalid syntax near !.

Help!!!!!!!

Signature

Learning is Never ending. So is certification.

Reply to this Message

Sylvain Lafontaine - 17 Feb 2006 04:16 GMT

Probably missing blank spaces.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> Whats wrong with this Private Sub Save_Record_Click()
> Dim SQL As String
[quoted text clipped - 15 lines]
>
> Help!!!!!!!

Reply to this Message

Roger - 17 Feb 2006 04:26 GMT

Signature

Learning is Never ending. So is certification.

> Probably missing blank spaces.
>
[quoted text clipped - 19 lines]
>
> Thanks for that

Heres one a little more challengingI assume that the from line is wrong as
well. I need to tansfer all data from this form to a table - about 7
controls. What do i need to change for this to work.
I only put 2 here for brevity.

Much appreciated

Reply to this Message

Sylvain Lafontaine - 17 Feb 2006 06:46 GMT

Forget about my last answer, the missing blank spaces and name of the
control after the name of the form got me distracted. Here is your correct
solution for two fields:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select '" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "'"

Notice that the values of adm_name and Problem have been delimited by single
quote ' because they are probably string characters from their name. If
they are integer values, then drop these single quotes. Another solution
would be to use the keyword Values instead of a Select statement:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " Values ('" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "')"

The Select statement can be used to insert multiple records in a single
statement, however you cannot use this feature here because it requires that
the source is a table; not a form or a list of values.

Finally, make sure that the SQL string is correct by displaying it in a
message box.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

>> Probably missing blank spaces.
>>
[quoted text clipped - 26 lines]
>
> Much appreciated

Reply to this Message

Sylvain Lafontaine - 17 Feb 2006 04:20 GMT

Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on the
SQL-Server. You must convert the value before calling DoCmd.RunSQL:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

If this value is a string, then you must not forget to enclose it between
single quotes.

The name of the forms is also missing, should be:

... & forms!Name_of_the_Form!frm_tick_entry

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> Whats wrong with this Private Sub Save_Record_Click()
> Dim SQL As String
[quoted text clipped - 15 lines]
>
> Help!!!!!!!

Reply to this Message

Roger - 20 Feb 2006 03:26 GMT

Is this in conjunction with the last posting which includes the sql coding.

If so then I cannot do what i want because I am referring to several text
boxes on one form and not just one. OR can i like it together and transfer
end box indiviually.

Would this work
Insert Into log
set admin = adm_name, issue=problem
from form!frm_tick_entry

Or is it the same problem.

Thankyou for your assistance - thiis is driving me mad.

I had tried the sql from the other posts - nothing happens at all - doesnt
even throw up an error.

Signature

Learning is Never ending. So is certification.

> Also, you cannot use "from forms!frm_tick_entry" inside a T-SQL on the
> SQL-Server. You must convert the value before calling DoCmd.RunSQL:
[quoted text clipped - 29 lines]
> >
> > Help!!!!!!!

Reply to this Message

Sylvain Lafontaine - 20 Feb 2006 21:04 GMT

Hi,

I'm not sure to which "last posting" you are making a reference. Is
this the one with the following piece of code:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select '" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "'"

or:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " Values ('" & forms!frm_tick_entry![adm_name] & "', " _
& "'" & forms!frm_tick_entry![Problem] & "')"

These two exemples have two fields, so there wouldn't be any real difference
if you want to have more.

And now about your last example:

SQL = "INSERT INTO log ([Admin],[Issue])" _
& " select [adm_name],[Problem]" _
& " from " & forms!frm_tick_entry"

This one cannot work because you cannot make a Select statement from a Form
because SQL-Server doesn't know anything about the forms on your local
machine. (Don't forget that all SQL code are running "remotely" on the
server, even when the server is on the same local machine.)

My two exemple above build the whole sql string using all required values
from the form. When this string has been built, it can be sent to the
server to be run there.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> Is this in conjunction with the last posting which includes the sql
> coding.
[quoted text clipped - 49 lines]
>> >
>> > Help!!!!!!!

Reply to this Message

Roger - 21 Feb 2006 02:04 GMT

Thanks for that - you have giivejn me an essential piece of information
about where the query acvtually runs.

BUT IT STILL DOESNT RUN - not your fault.

Where should this click reside, currently on the on click function in a
command button (as cose)!

Would an "autonumber field in the table be interfering.

Is there ANYWHERE else should i be looking. No errors are thrown up or
anything indicating the problem.

I am the virge of giving up on this.

Thanks for all the help you have given me.

Signature

Learning is Never ending. So is certification.

> Hi,
>
[quoted text clipped - 82 lines]
> >> >
> >> > Help!!!!!!!

Reply to this Message

Sylvain Lafontaine - 21 Feb 2006 02:52 GMT

This is a big difference between ADP and MDB. With MDB, excerpt for the
special case of sql pass-through, everything run locally inside jet while
with ADP (or SQL Pass-through), everything run remotely on the SQL-Server.

If it doesn't work, then you should show us the final result for the sql
string, just before you try to execute it on the server; to make sure that
there is no syntax error. (Personally, I use Query Analyser but I don't
know if you have it.)

And Yes, an autonumber can be interfering: you should not try to include a
value for the autonumber when making the insertion. (There is a special
command for doing this but this is clearly not your case.)

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> Thanks for that - you have giivejn me an essential piece of information
> about where the query acvtually runs.
[quoted text clipped - 106 lines]
>> >> >
>> >> > Help!!!!!!!

Reply to this Message

Roger - 21 Feb 2006 03:34 GMT

tHANKS FOR YOUR HELP

Ran this in query analyser
INSERT INTO log ([Admin],[Issue])
select '" & Forms!frm2![adm_name] & "', '" & Forms!frm2![Problem] & "'

Get this answer

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'log'.

This is run directly on the sql server

The table name is log though, the sql server shows dbo.log but when changed
the error is still the same.

Signature

Learning is Never ending. So is certification.

> This is a big difference between ADP and MDB. With MDB, excerpt for the
> special case of sql pass-through, everything run locally inside jet while
[quoted text clipped - 119 lines]
> >> >> >
> >> >> > Help!!!!!!!

Reply to this Message

Sylvain Lafontaine - 21 Feb 2006 04:02 GMT

Store the sql string into a string variable and show it's value in a message
to make sure that everything is OK.

Also, log is the name of mathematical function, so you will have to enclose
with square brackets: try with [dbo].[log] instead of just log.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> tHANKS FOR YOUR HELP
>
[quoted text clipped - 147 lines]
>> >> >> >
>> >> >> > Help!!!!!!!

Reply to this Message