Prev: Access 2010 webbrowser control
Next: Retrieve selected value from ListBox
From: Tom van Stiphout on 8 Jun 2010 22:12
On Tue, 8 Jun 2010 15:09:33 -0700 (PDT), Roger
<lesperancer(a)natpro.com> wrote:

I would recommend you implement the transaction in a stored procedure.
You would create a Linked Server so the main SqlServer knows about the
other one, and you can write queries using a 4-part name:
select * from myLinkedServer.myDatabase.dbo.myTable

Implement your transaction in the stored procedure, and make sure
MSDTC = Distributed Transaction Coordinator is running, and you will
have the full protection of transactions.

-Tom.
Microsoft Access MVP




>On Jun 8, 3:35�pm, Rich P <rpng...(a)aol.com> wrote:
>> Historically, ODBC has been problematic for performing action queries
>> (Inserts/Updates/Deletes) on tables contained in a Sql Server DB from
>> Access. �There are workarounds, but I find the easiest technique is to
>> use ADO when manipulating Sql Server objects from Access. �Here is a
>> routine you could try out
>>
>> note: �you need to make a reference in a code module from Tools/Rerences
>> to
>>
>> Microsoft ActiveX Data Objects 2.5 (or higher) Library
>>
>> Sub ActionQueriesOnSqlServer()
>> � Dim cmd As New ADODB.Command, j As Long
>>
>> � '--if you are using Windows authentication you can use
>> � '--this connection string
>> � 'cmd.ActiveConnection = "Provider=SQLOLEDB; Data
>> Source=yourServer;Database=YourDB;Trusted_Connection=Yes"
>>
>> � '--if you are using Sql Server authentication then use
>> � '--this connection string
>> � cmd.ActiveConnection = "Provider=SQLOLEDB; Data
>> Source=YourServer;Database=YourDB;UID=steve;password=tiger;"
>>
>> � cmd.ActiveConnection.CursorLocation = adUseClient
>> � cmd.CommandTimeout = 600
>> � cmd.CommandType = adCmdText
>>
>> � cmd.CommandText = "Delete tblx Where ID = " & txtID
>> � cmd.Execute j, , adExecuteNoRecords
>> � Debug.Print j & " records Deleted"
>>
>> � cmd.CommandText = "Update tblx seled fld1 = 'test' Where ID = " &
>> txtID
>> � cmd.Execute j, , adExecuteNoRecords
>> � Debug.Print j & " records Updated"
>>
>> � cmd.ActiveConnection.Close
>>
>> End Sub
>>
>> Rich
>>
>> *** Sent via Developersdexhttp://www.developersdex.com***
>
>looks good, but I just remembered, some tables are on one sql server
>and other tables are on a second sql server
>and both need to be updated within the same transaction, can I do that
>with ADO ?
 | 
Pages: 1
Prev: Access 2010 webbrowser control
Next: Retrieve selected value from ListBox