using T-SQL in Access

[Machiaveli]

Hi,

How can i use a transact-sql like an update statement in an msaccess form?

I have a form with a listbox and a table. By choosing a name in the listbox, the empty field in the table must be updated.
I import large accessdb's but now i'm working with
sql server. How can i automate this proces where i want to update also other values on the fly.

Greetings,

 

[scriverb]

You can create a SQL string with a reference to the combo box value and update a particular record. You will however have to indicate the record to be updated.

Example:

Update [i]yourtable[/i]as A SET A.[[i]fieldtoupdate[/i]] = FORMS![[i]yourformname[/i]]![[i]comboboxname[/i]] WHERE A.[[i]recordIDfield[/i]] = [red]somevalue[/red];

In the AfterUpdate event procedure of your combobox put code in that reads like this example:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryYourUpdateQueryName"
DoCmd.SetWarnings True

Post back if you need more assistance.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Machiaveli]

hi scriverb,

I already have your example in access, but i want to know how to call a sql server stored procedure for updating etc.

I use the dts in sql server to import, but i would like to execute it from a form in access

is this possible?

 

[scriverb]

Check out the specifics of a pass-through query. The SQL entered in a pass-through query is in not ACCESS SQL but rather the syntax of the SQL server. You can call your stored procedures from with this ACCESS pass-through query. It connects through your ODBC connection and passes the code directly to that server and runs the code there. Calling up a stored procedure is certain possible.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[smuckers]

Good Morning Machiaveli:

This has worked for me with very low maintenance.

1. In Query Analyzer, start by verifying the proper functioning of a stored procedure calling a DTS package.

2. In Access, use the command ADO object to pass in parameters(s) and to run the stored procedure. Be sure to set up your variables in the stored procedure correctly.

2b. Note: Running a stored procedure on SQL Server optimizes the running of the query and allows SQL Server to do what it was designed to do, heavy data processing. Ad Hoc queries via "pass-through" queries are not as efficient.

3. Using the command ADO object, create and append parameters before executing the query.

4. Tek-tips, MSDN and MS Access Help all provide specific examples on how to call a stored procedure using a command object. There are various ways of doing it based upon the situation, but they all create, append and call a stored procedure.

Good luck and troubleshoot your process one step at a time.

Smuckers.

May your hysterical randomness be cured!

 

[Machiaveli]

Hi smuckers,

Can you give me an example how to call a ado object command?