1

Topic: ADO and local table Access

Dear experts.
tblLocal - the local table in Access
tblServer - the table on the server ( them it is impossible)
Request of a type
INSERT INTO tblLocal (F1, F2) SELECT F1, F2 FROM [ODBC; Driver = {A SQL Server}; Server =...; Database =...; UID =...; PWD =...;].tblServer
Works perfectly, but the reverse situation interests:

Set obDB = New ADODB.Connection
With obDB
.Provider = "SQLOLEDB"
.ConnectionString = "User ID =...; Password =...; DATA SOURCE =...; INITIAL CATALOG =..."
.Open
End With
Set obReport = New ADODB.Recordset
With obReport
.ActiveConnection = obDB
.Open INSERT INTO tblLocal (F1, F2) SELECT F1, F2 FROM tblServer
End With

Whether it is possible to specify somehow the local table in such request and if it is possible as it to make?

2

Re: ADO and local table Access

Uralec;
:
http://www.sql.ru/forum/afsearch.aspx?s … &bid=4

3

Re: ADO and local table Access

ROI;
Looked through, but the answer to a question, unfortunately, did not find.

4

Re: ADO and local table Access

Uralec wrote:

ROI;
Looked through, but the answer to a question, unfortunately, did not find.

There all is how to work with tables through ADO.

5

Re: ADO and local table Access

ROI wrote:

it is passed...
There all is how to work with tables through ADO.

, strange you "before shout"...
you  on demand "SQL" did not send, is asked? smile
The HARDWARE, I do not know that you  in  with this task, but she dares similarly to your decision for

INSERT INTO [ODBC; Driver = {A SQL Server} ;Server=myserver;Database=mydb;UID=sa;PWD=mypassword;].T (id, txt)
SELECT id, txt from T;

As you wanted to make that "from start",  the provider of MSEC  - too basically is solved, through any MSEC SKL TH openrovset/openkveri, but it on the order  it to whom is necessary! smile
And all these ,  and other JDBC only a method of dialogue of the client with
It is no more!
If the DB is able to work with heterogeneous requests  as you "will ask" it to fulfill it smile

6

Re: ADO and local table Access

court;
Thanks for the response but how correctly to fill a line of connection I did not understand:
All it is in a file accdb (2007 office) which lies on a computer hard disk
Therefore what to specify as all these parameters not clearly.
Server - a way to a file?
Database - file name accdb?
The user is not present (only if security account Windows)
The password too is not present
Tried also other driver, too did not grow together.
because besides I know nothing. It so for itself, for simplification of operation.
Access as I understood, can work with heterogeneous requests, if I correctly understood that this such but how to be anchored to the local table being in accdb a file I will not understand.

7

Re: ADO and local table Access

Uralec wrote:

Request of a type

INSERT INTO tblLocal (F1, F2) SELECT F1, F2 FROM [ODBC; Driver = {A SQL Server}; Server =...; Database =...; UID =...; PWD =...;].tblServer

Works perfectly, but the reverse situation interests:

Explain, what this you designates "a reverse situation"?
It is necessary for you from the local/aktsessovskoj table tblLocal, to flood the data in the table tblServer on  the Server, or what?
Or "the reverse situation" designates operation in a connection to the server, instead of from Aktsessa??

8

Re: ADO and local table Access

court;
Is not present on the contrary, to me it is necessary tblLocal to flood in the local/aktsessovskuju table the data from the server.
I can be connected to this server only using ADO;
Or using a construction: INSERT INTO tblLocal (F1, F2) SELECT F1, F2 FROM [ODBC; Driver = {a SQL Server}; Server =...; Database =...; UID =...; PWD =...;].tblServer
But a problem that on the server certainly tables a cloud and all them to specify in requests so it is long problematic. Plus through ADO I can give on the server requests through commands, and the result of these requests should lay down in the local table in  a file.
I hope clearly explained.

9

Re: ADO and local table Access

court;
Here I opened  a connection
Set obDB = New ADODB.Connection
With obDB
.Provider = "SQLOLEDB"
.ConnectionString = "User ID =...; Password =...; DATA SOURCE =...; INITIAL CATALOG =..."
.Open
End With
Here obtained the necessary data in
Set obReport = New ADODB.Recordset
With obReport
.ActiveConnection = obDB
.Open SELECT F1, F2 FROM tblServer
End With
Also there is a question as being in  connection to the server to refer to the local table in  a file, to throw off there the data.

10

Re: ADO and local table Access

In any way, it is necessary to open other connection to yours  to a file, and then in a cycle to transfer the data line by line from one  in another.
Or as the variant   basis on the server and to work there directly

11

Re: ADO and local table Access

Konst_One;
Thanks, such I know a method. Simply it would not be desirable to sort out a cycle, and the beautiful decision at once to flood one request, well and if it is not possible it is necessary so to do.
To that server at me only on reading and about any associations even speech anybody will not conduct access.

12

Re: ADO and local table Access

Then your way -  the necessary tables of the server in yours

13

Re: ADO and local table Access

Konst_One;
Yes, the server I , but this server I even  cannot.
Being in  connection the table of the server I specify in request as:
[Server]..dbo.
And all works. Thought there is a same decision and for the local table.

14

Re: ADO and local table Access

Uralec wrote:

Konst_One;
Yes, the server I , but this server I even  cannot.
Being in  connection the table of the server I specify in request as:
[Server]..dbo.
And all works. Thought there is a same decision and for the local table.

It, most likely,  to "your server", other MSEC  the Server.
Create, once, in the DB of Aktsessa, "server request", (to "your server"!) where will be

select * from [Server]..dbo.

And further use this request instead of this, [Server]..dbo., tables everywhere where it is necessary

15

Re: ADO and local table Access

Uralec;
"Server request", suddenly , it here this "thingummy"
[img=https://www.wikihow.com/images/thumb/b/b4/Run-a-Query-in-MS-Access-2007-Step-5-Version-2.jpg/aid2378215-v4-728px-Run-a-Query-in-MS-Access-2007-Step-5-Version-2.jpg]

16

Re: ADO and local table Access

court;
In vain I about other server wrote, the confusion only turns out.
I have 3 types of tables:
1. Tables on the server accessible only through ADO
2. Tables on my server, which  to a DB of Aktsessa.
3. Local tables in a DB of Aktsessa.
I do ADO connection to the server of point No1, I do request to tables of the server No1, I obtain the data and in the same request I throw off them on the server No2. That is  2 servers in one request at me at the very least turn out.
And here the local table (point No3) and the server accessible only through ADO (point No1), no.
Not absolutely understood your sentence into the query design account in a DB of Aktsessa. I cannot in ADO address connection to the local table (a DB of Aktsessa) and also I can not address to local request.

17

Re: ADO and local table Access

court;
The interesting button, in something helps.
It turns out replaced ADO connection to the inaccessible server.
Interesting and here such it is possible to push somehow through such request:

Set cmd = New ADODB.Command
cmd. ActiveConnection = cnn
cmd. CommandText = "delete from pResource where spid = @@ spid"
cmd. CommandType = adCmdText
cmd. Execute
cmd. CommandText = "insert pResource (spid, ResourceID)" AND _
"select @@ spid, ResourceID" AND _
"from tResource" AND _
"where len (replace (Brief, ' ',")) = 20 and "AND _
"BalanceID in (2140, 2123, 55015845) and" AND _
"(DateEnd <= current_timestamp or DateEnd = ' 19000101 ')"
cmd. CommandType = adCmdText
cmd. Execute
cmd. CommandType = adCmdStoredProc
cmd. CommandText = "AccList_Rest"
cmd. Parameters. Item ("@Date") = iDate
cmd. Execute
DoCmd. SetWarnings False
DoCmd. RunSQL "delete from BalData_serv"
cmd. CommandText = "insert into [server].bd.dbo. BalData (AccName, AccBrief, Rest, RestBs, Fund, Type, iDate) select r. Name as AccName, r. Brief as AccBrief, abs (rl. Rest) as Rest, abs (rl. RestBs) as RestBs," AND _
"SUBSTRING (replace (s. ISONumber, ' ',"), 1,3) as Fund, "AND _
"r. CharType as Type," AND DDSQL (iDate) AND "as iDate" AND _
"from pResList rl inner join" AND _
"tResource r on rl.spid = @@ spid and rl. Rest! = 0 and rl. ResourceID = r. ResourceID inner join" AND _
"tCurrency s on rl. FundID = s. CurrencyID"
cmd. CommandType = adCmdText
cmd. Execute

18

Re: ADO and local table Access

Uralec wrote:

1. Tables on the server accessible only through ADO

As it? Login/password do not give, or what reason?

Uralec wrote:

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.

Uralec wrote:

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

19

Re: ADO and local table Access

court;
"How it? Login/password do not give, or what reason?"
At attempt  the table: the Exterior data - In addition - Database ODBC
I receive the message:
ODBC - a call error
Request wait time (#0) effused
Also there is an empty window "Communication with tables"
Why so to me it is not clear.
Thus as you showed in "server request" I use too the connection and all works.
You ADO-konekshion (cnn), probably somewhere is opened globally, and all time "keeps" opened.
I do not know how much globally, simply it opened before all it commands and closed after their performance. I therefore turn out in one session I am.
With simple requests you very much helped me, it is possible to use "server request", and with it commands I will understand further.
Thanks big.