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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.