OK I need help again adding form data to SQL and back ??

[RON]

TC thanks for the help so far but I am still not getting anywhere.

In regards to linking tables I cant do this because my Access database does
not have any tables at all.

Lets say this is what I want to do.

I've got an SQL Server table "UserInfo"
in this table there are fields "ID" , "Name" , and "Phone"

I then have a couple forms in access, still my access database has No
tables, just two forms.

The first form "FINDUSER" consists of a textbox for "ID" "Name" and "Phone"
If I enter a ID into the textbox and hit my button to find I want the Name
and Phone textboxes to be populated with the correct infor for that user
from the sql table, thats all.

Another form, INSERTUSER has all of the same text boxes, now when the
Insert button is pressed on this form I want the ID, Name and Phone that are
entered in the textboxes to go over and be entered in the SQL Table.

thanks
Maybe someone has an access database somehwere that does something similar
that I could look at or how would I accomplish this?

thanks

 

[Guest]

Hi Ron,
I write all my ACCESS apps using MS SQL Server, well, any with any size that
it makes sense to I guess.

I have an upfont question for you though, do you want to access the table
directly, or do you want to write a stored procedure in the DB to access the
rows for you? Personaly, I use the stored procs, that way I don't have to
bring the whole table thru the wire. Your preference though.

Oh, and you mentioned the linking part. I still link the ACCESS apps to the
SQL tables sometimes also. That way you can code a standard SELECT statement
like you are used to doing with jet tables, and it won't know the difference.
You may want to try that just to get going. To do this you will need to
create an ODBC connection from your ACCESS app to your SQL database. Then
when you link them, in the Files of Type drop down, choose ODBC Databases,
then use the Machine Data Source tab, and chose your ODBC connection you
made. Then they link just like you linked to an .mdb.

Others may add better ways than mine, but it works.

HTH
Steve

 

[paul814]

I need to use an UNBOUND for by the way, and yes a stored procedure
would be a good way to do it.

I have no idea what the code would look like to do this or the stored
procedure in SQL though.

 

[Guest]

Hi,

You can go to Stored Procedures in your SQL Server database. Right click on
it and select New Stored Procedure. Here is the Stored Proc code in its
entirety:

CREATE PROCEDURE [dbo].[spUserInfoSelect]
AS

SET NOCOUNT ON

SELECT ui.ID, ui.Name, ui.Phone
FROM dbo.UserInfo ui
ORDER BY ui.ID

RETURN

GO


Save this. then go to ACCESS and go to Queries, you will see your stored
proc there, (may have to hit refresh F5). Then go create a form with the
wizard, and se t the record source to your stored proc, it will be in the
list. Then you have a form based on the stored proc reading you SQL table.

As you update record through your form, you are updating the SQL table, even
though you did not write an UPDATE step.

Certainly others will have other methods, but if you want to get your ACCESS
adp to be based on MS SQL Server, this will get you going.

OH, just thought of something. You are using an Access Data Project, not an
..mdb, right? An adp is what lets access hook to MS SQL. If you are NOT
using and ADP, you will need to link to the SQL tables via an ODBC
connection. You need to use an ADP to use the stored procs.

HTH
Steve