MS Access vba Insert data into SQL Server table based on textbox value

deer777
deer777 used Ask the Experts™
on
MS Access vba create query to Insert data into SQL Server table based on textbox value
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
What is your concrete problem?

E.g.

CurrentDb.Execute "INSERT INTO myTable ( Payload ) VALUES ( '" & Replace(Nz(txtTextBox.Value, ""), "'", "''") & "');"

Open in new window

Author

Commented:
Have an Access database with linked sql server table.  

In Access I have a main form with a textbox that allows user to input number into which will populate another form datasheet (from linked SQL table).  Need to dump data into another linked SQL table from the data defined by the textbox value in Access by writing a stored procedure.  just need to know how to define this textbox value in SQL Server query
Senior Developer
Commented:
Linked tables are transparent to ACE, thus they behave like normal tables. Just use the same techniques like you would do in a plain Access solution.

Stored procedures are on the SQL Server side and not visible to Access. You need either to run a pass-through query to execute them or use a ADO.Command to execute it directly against the server. Problem in the second case is that you will work on different connections for sure, while using pass-through queries allow connection reuse.

E.g. something like

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb	 
Set qdf = db.CreateQueryDef("")

qdf.Connect = db.TableDefs("AnyLinkedTable").Connect
qdf.ReturnsRecords = False
qdf.SQL = "EXECUTE yourProcedure '" & Replace(Nz(txtTextBox.Value, ""), "'", "''") & "';"
qdf.ODBCTimeout = 300
qdf.Execute

set qdf = Nothing
set db = Nothing

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial