Newby - needing Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have an sql2000 backend and wish to use access2003 for a front end.

I have created the sql tables required and inan access adp have link etc the
tabled ODBC.

The questionis that I want to fill out my form and when its completed click
a "save" button to transfer the data in the form to the sql table (held on
remote server). We will have upto 4 ppl entering data at any one time.

Does any one forsee a problem with this set up.

How do I transfer the datat - do I use an INert into, update qry, or is
there better?

Any help is appreciated.

Thankyou
 
Dear Roger:

Using ODBC I guess you're planning to write the Access application as an
MDB. Another alternative is the ADP which is native to SQL Server.

Either way, the basic capacity to access the SQL Server data from tables or
views, and to save changes and new records (inserts, updates, and deletes)
is built into Access and is transparent to the designer under most
circumstances, unless you choose otherwise (such as unbound forms)..

I do not see any general problems with this approach for you. There can be
some challenges in certain peculiar specifics, but in general it is just
done for you.

Tom Ellison
 
Sorry Tom - i thought my reply went through.

I want an unbound form to write back to an sql table.,

Currently save / created the object as ADP. But stuck from here.

I have created a code (in another post) but it aint working.

Nothing really seems to be working at all - will work through details.

Camn you pick what is 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
No matter what I try i pops errors in different places.

Thanks for any help!!!
 
Dear Roger:

Yes, I can see the problem. The SQL code, when concatenated, reads:

INSERT INTO log ([Admin].[Issue])select [adm_name].[Problem]from
forms!frm_tick_entry

Just because you come to the end of a line in the VBA code does not put a
new-line into the SQL code. You need to add at least a space at the end of
each line of VBA code when you are between words in SQL. In this case, it
may be the case you can get away with it, however, as it may work as shown,
since the missing spaces follow a ] in each case, which it may be able to
parse correctly. But this is a very important habit to obtain.

Place a breakpoint on the line after the line that assigns the SQL string,
and look at the value of the string in a watch or immediate pane. You'll
see exactly what is going on. You can paste this into a new query and test
to see it works, or just see what is wrong from how you built it.

The other item is that final reference:

forms!frm_tick_entry

This is a reference to a specific form. Does a form have a value? I think
it likely you mean to refer to a specific control on that form, which may
have the value you want to use in this query. What you change is this:

forms!frm_tick_entry!control_name

I assume the control you wish to reference is on the named form.

Did this help?

Tom Ellison


Roger said:
Sorry Tom - i thought my reply went through.

I want an unbound form to write back to an sql table.,

Currently save / created the object as ADP. But stuck from here.

I have created a code (in another post) but it aint working.

Nothing really seems to be working at all - will work through details.

Camn you pick what is 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
No matter what I try i pops errors in different places.

Thanks for any help!!!
--
Learning is Never ending. So is certification.


Tom Ellison said:
Dear Roger:

Using ODBC I guess you're planning to write the Access application as an
MDB. Another alternative is the ADP which is native to SQL Server.

Either way, the basic capacity to access the SQL Server data from tables
or
views, and to save changes and new records (inserts, updates, and
deletes)
is built into Access and is transparent to the designer under most
circumstances, unless you choose otherwise (such as unbound forms)..

I do not see any general problems with this approach for you. There can
be
some challenges in certain peculiar specifics, but in general it is just
done for you.

Tom Ellison
Click to expand...
Click to expand...
 
Thanks Tom - looks like i am barking up the wrong tree, or just barking mad.

What i have is a form with several unbound text boxes and want to copy data
from all text boxes into the table, i have only refered to 2 controls for
testing) so refering to a single control wont do it - I may need to delete
the last line. The script is on a button with-in the form.

As with the space issue - a stupid error, hard to pick when broken over
lines, thanks for that.

Much appreciate you help on this


--
Learning is Never ending. So is certification.


Tom Ellison said:
Dear Roger:

Yes, I can see the problem. The SQL code, when concatenated, reads:

INSERT INTO log ([Admin].[Issue])select [adm_name].[Problem]from
forms!frm_tick_entry

Just because you come to the end of a line in the VBA code does not put a
new-line into the SQL code. You need to add at least a space at the end of
each line of VBA code when you are between words in SQL. In this case, it
may be the case you can get away with it, however, as it may work as shown,
since the missing spaces follow a ] in each case, which it may be able to
parse correctly. But this is a very important habit to obtain.

Place a breakpoint on the line after the line that assigns the SQL string,
and look at the value of the string in a watch or immediate pane. You'll
see exactly what is going on. You can paste this into a new query and test
to see it works, or just see what is wrong from how you built it.

The other item is that final reference:

forms!frm_tick_entry

This is a reference to a specific form. Does a form have a value? I think
it likely you mean to refer to a specific control on that form, which may
have the value you want to use in this query. What you change is this:

forms!frm_tick_entry!control_name

I assume the control you wish to reference is on the named form.

Did this help?

Tom Ellison


Roger said:
Sorry Tom - i thought my reply went through.

I want an unbound form to write back to an sql table.,

Currently save / created the object as ADP. But stuck from here.

I have created a code (in another post) but it aint working.

Nothing really seems to be working at all - will work through details.

Camn you pick what is 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
No matter what I try i pops errors in different places.

Thanks for any help!!!
--
Learning is Never ending. So is certification.


Tom Ellison said:
Dear Roger:

Using ODBC I guess you're planning to write the Access application as an
MDB. Another alternative is the ADP which is native to SQL Server.

Either way, the basic capacity to access the SQL Server data from tables
or
views, and to save changes and new records (inserts, updates, and
deletes)
is built into Access and is transparent to the designer under most
circumstances, unless you choose otherwise (such as unbound forms)..

I do not see any general problems with this approach for you. There can
be
some challenges in certain peculiar specifics, but in general it is just
done for you.

Tom Ellison


Hi all,

I have an sql2000 backend and wish to use access2003 for a front end.

I have created the sql tables required and inan access adp have link
etc
the
tabled ODBC.

The questionis that I want to fill out my form and when its completed
click
a "save" button to transfer the data in the form to the sql table (held
on
remote server). We will have upto 4 ppl entering data at any one time.

Does any one forsee a problem with this set up.

How do I transfer the datat - do I use an INert into, update qry, or is
there better?

Any help is appreciated.

Thankyou
Click to expand...
Click to expand...
Click to expand...
 
Back
Top