Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert into stmt with parameters Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 4:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
Hi,

I have been checking all over for this solution and I know the follow:
Server SQL will not reference ms access forms... Such as forms!blah!blav

One way to use parameters in a form or stored procedure is to have input parameters set...

Now I am making this post twice, I know but I would like to know if this is possible or if I'm chasing after a wild goose here...

I have am ms access for that has an unbound text box.
When the user click save, I want the data from the text box put into a table.

Is this possible?
Post #1377329
Posted Tuesday, October 30, 2012 1:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 2:22 PM
Points: 54, Visits: 86
You don't need to reference your access form into SQL Server to create new records from
your form's textboxes. Please Link your SS Table in MS Access and Write Code to Insert a
new record from your Form into SS.

Since you haven't provided any detail, here is a sample code you need to write. I am assuming that
you have an average level of VBA programming knowledge.

Lets say you are adding a new customer in Customer Table which is linked from Sql Server.
Please Write code under Click event of your SAVE button on the form FrmCustomer. The purpose is
when user click save button then all the values from Textboxes to your SQL Server Customer Table.

- Customer Table has FirstName and LastName Columns
- FrmCustomer has TxtFirstName and TxtLastName Textboxes

Dim RsCustomer as Recordset
Set RsCustomer = Currentdb.openrecordset("Select * from Customer")

RsCustomer.Addnew
RsCustomer!FirstName = FrmCustomer!TxtFirstName.Value
RsCustomer!LastName = FrmCustomer!TxtLAstName.Value
RsCustomer.Update


There are few things you need to do :
- Open recordset as dynaset
- Have a primary key to your SS table and identify when link table in Access

Above two steps makes your table writable otherwise it will open as a snapshot/readonly.

Post #1378995
Posted Tuesday, October 30, 2012 5:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
Thanks for the reply. I got a really good solution to this.

Appreciate your help.

The idea is to use ms access accdb file as front end rather than adp

Gives lots of flexibility.

Working on it right now. I never been to a computer class so it's all trial and error.

If you would like to know what the solution is, I can post it...I sure other people would want to know about it too.


Post #1379096
Posted Tuesday, October 30, 2012 8:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 2:22 PM
Points: 54, Visits: 86
Yes thats the idea making your accessdb as a frontend and you will be linking table from sql server into MS Access.
All you need to do coding using DAO recordset and it's method. Feel free to post if you get stuck somewhere or if
you need code help. Please post your notes with sufficient details.

Post #1379138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse