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

MS Access vba create query to Insert data into SQL Server table based on textbox value
deer777Asked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
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

0
 
ste5anSenior DeveloperCommented:
What is your concrete problem?

E.g.

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

Open in new window

0
 
deer777Author 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.