1. Tables on the server accessible only through ADO
As it? Login/password do not give, or what reason?
And here the local table (point No3) and the server accessible only through ADO (point No1), no.
possibility is, but it not so "", ..
In a connection to the server, for record in the Aks-table, it is possible to use OPENROWSET (above already spoke)
Something of type:
insert into OPENROWSET (' Microsoft. ACE.OLEDB.12.0 ', ' \\sqldev01\temp\test.accdb '; ' admin '; ", tblLocal)
select * from tblServer
Only I do not advise with this "possibility" even to communicate. Too much "if" it should be fulfilled that the request would work as this:
- On the server should be resolved ad ho requests
- On the server the provider should be installed ACE. Also the same , as the server (and to deliver both 64, and 32 simultaneously it is impossible)
- The provider needs to install 2 any "correct"
- And the main thing, at login under which is fulfilled request there should be sufficient rights to file access Aks from the server.
interesting and here such it is possible to push somehow through such request:
It, basically, is possible.
You write all requests in SQL this "server requests", through ";" (it is possible program through VBA) and you fulfill (besides it is possible from VBA). Only parameters, in that case, will need to be registered in request at once literals.
But only it is is not specific in your case.
You ADO-konekshion (cnn), probably somewhere is opened globally, and all time "keeps" opened.
Otherwise such variants:
cmd. CommandText = "delete from pResource where spid =>>> @@ spid <<<"
cmd. CommandText = "insert pResource (spid, ResourceID)" AND _
"select>>> @@ spid <<<, ResourceID" AND _
would not transit.
And so, "server request", as a result, will be fulfilled in other connection, with another @@ spid