Problems in SQL

[Diogo]

Hello.

I have this code and the first sql it returns me data, in the second give's me the error "A syntax error has occurred"

Set Mail = New ADODB.Connection
Set Mail_Rec = New ADODB.Recordset
Mail.ConnectionString= "DSN=Teste;UID=teste;PWD=teste;DATABASE=teste"
Mail.Open

Mail_Rec.Open &quot;select t_cuno,t_nama,t_net from ttccom010200 where t_net<>'' order by 1&quot;, Mail

Mail_Rec.Open &quot;select t_cuno,t_nama,t_net INTO cliente IN 'C:\mail.mdb' from ttccom010200 where t_net<>'' order by 1&quot;, Mail

There is someone can help me?

Thanks any way.

Diogo Reis

 

[mmayo]

Hi Diogo -

I've done something similar. But for it to work, the ADO connection needs to be to the database containing the table that will be receiving the data. I haven't checked the following code so you would need to adjust to your DSNs and database locations, but your end result should look something like this:

With Mail
.CursorLocation = adUseClient
.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Mail.mdb;Persist Security Info = False&quot;
End with

Dim strSQL as string
Dim strDbl as string 'to insert double quotes

strDbl = chr(34)

strSQL = &quot;INSERT INTO cliente (t_cuno, t_nama, t_net) FROM ttccom010200 IN &quot; & strDbl & strDBL & &quot;[ODBC; provider=MSDASQL.1;DSN=Teste;UID=teste;PWD=teste;DATABASE=teste;] WHERE ttccom010200 <>' ';&quot;

Mail.execute strSQL

 

[Diogo]

Thanks mmayo!

I understand you, but with you code (i'm new in this tipe of questions) it give's one error.

error instruction INSERT INTO.

strSQL = &quot;INSERT INTO cliente (t_cuno, t_nama, t_net)&quot;
strSQL = strSQL + &quot; FROM ttccom010200 IN &quot; & strDbl
strSQL = strSQL + &quot; [ODBC;DSN=Informix HA701;UID=informix;PWD=risc6000;DATABASE=b4c4;]&quot; & strDbl
strSQL = strSQL + &quot; WHERE ttccom010200 <>'';&quot;

Can you help me?

 

[mmayo]

I would have felt much better if this had worked!

Just so you know, I'm using this format to pull data from PervasiveSQL into access. It took me several tries to get this to work in my environment. I haven't tried this with informix. After that disclaimer, perhaps try:

strSQL = &quot;INSERT INTO cliente (t_cuno, t_nama, t_net) &quot;
strSQL = strSQL & &quot; FROM ttccom010200 IN &quot;
strSQL = strSQL & strDbl & strDbl
strSQL = strSQL & &quot; [ODBC;DSN=Informix HA701;UID=informix;PWD=risc6000;DATABASE=b4c4;]&quot; & strDbl
strSQL = strSQL & &quot; WHERE ttccom010200 <>' ';&quot;

(Note that I'm using two single quotes after <>.)

 

[Diogo]

I'd cut and paste it in my project but it doesn't work.

Could be because i in ttccom010200 have many fields, the table cliente is diferent from the ttccom010200?

In the SQL there's no select to the table ttccom010200. Is this correct? I've tried but i can't?

Thanks any way.

DR

 

[mmayo]

Good catch! You are absolutely correct. You need to select the specific fields:

strSQL = &quot;INSERT INTO cliente (t_cuno, t_nama, t_net) &quot;
strSQL = strSQL & &quot;SELECT ttccom010200.t_cuno &quot;
strSQL = strSQL & &quot;ttccom010200.t_nama &quot;
strSQL = strSQL & &quot;ttccom010200.t_net &quot;
strSQL = strSQL & &quot; FROM ttccom010200 IN &quot;
strSQL = strSQL & strDbl & strDbl
strSQL = strSQL & &quot; [ODBC;DSN=Informix HA701;UID=informix;PWD=risc6000;DATABASE=b4c4;]&quot; & strDbl
strSQL = strSQL & &quot; WHERE ttccom010200 <>' ';&quot;

 

[Diogo]

The error comes again.

Why do you use &quot;strSQL = strSQL & strDbl & strDbl&quot;?

 

[mmayo]

Oops. One other error, remove the strDbl at the end of the ODBC:

strSQL = &quot;INSERT INTO cliente (t_cuno, t_nama, t_net) &quot;
strSQL = strSQL & &quot;SELECT ttccom010200.t_cuno &quot;
strSQL = strSQL & &quot;ttccom010200.t_nama &quot;
strSQL = strSQL & &quot;ttccom010200.t_net &quot;
strSQL = strSQL & &quot; FROM ttccom010200 IN &quot;
strSQL = strSQL & strDbl & strDbl
strSQL = strSQL & &quot; [ODBC;DSN=Informix HA701;UID=informix;PWD=risc6000;DATABASE=b4c4;] &quot;
strSQL = strSQL & &quot; WHERE ttccom010200 <>' ';&quot;

As far as why use strSQL & strDbl & strDbl: someone shared it with me as a solution to get PervasiveSQL and Access to talk.

 

[Diogo]

The error don't let me alone.
I have done some changes.

strSQL = &quot;INSERT INTO cliente (t_cuno, t_nama, t_net) &quot;
strSQL = strSQL & &quot;SELECT ttccom010200.t_cuno, &quot;
strSQL = strSQL & &quot;ttccom010200.t_nama, &quot;
strSQL = strSQL & &quot;ttccom010200.t_net, &quot;
strSQL = strSQL & &quot; FROM ttccom010200 IN &quot;
strSQL = strSQL & strDbl & strDbl
strSQL = strSQL & &quot; [ODBC;DSN=Informix HA701;UID=informix;PWD=risc6000;DATABASE=b4c4;] &quot;
strSQL = strSQL & &quot; WHERE ttccom010200.t_net <>' ';&quot;

i'd put the commas after 2, 3, 4 line and put &quot;.t_net&quot; on the last one.

it looks dificult...

DR

 

[mmayo]

I have only one other idea and that would be your ODBC path.
I believe you are missing the provider. For example (and this truly works on my machine):

[ODBC;Provider=MSDASQL.1;DSN=Indust;ServerName=IndustriOS.1583;ServerDSN=Indust_T;UID=MM;PWD=xx;ArrayFetchon=1;arrayBufferSize=8;TransportHint=TCP;DecimalSymbol=.;]

 

[Diogo]

Thanks very much, but i can't.

With this code i can read the number of records:
With Mail
.CursorLocation = adUseClient
.ConnectionString=&quot;ODBC;Provider=MSDASQL.1;DSN=Informix HA701;UID=informix;PWD=risc6000;DATABASE=b4c4;] &quot;
.Open
End With
MailRec.Open &quot;select t_cuno, t_nama, t_net from ttccom010200 WHERE ttccom010200.t_net <>''&quot;, Baan
MsgBox MailRec.RecordCount

But with this i can't:
strSQL = &quot;INSERT INTO cliente (t_cuno, t_nama, t_net) &quot;
strSQL = strSQL & &quot;SELECT ttccom010200.t_cuno, &quot;
strSQL = strSQL & &quot;ttccom010200.t_nama, &quot;
strSQL = strSQL & &quot;ttccom010200.t_net, &quot;
strSQL = strSQL & &quot; FROM ttccom010200 IN &quot;
strSQL = strSQL & strDbl & strDbl
strSQL = strSQL & &quot; [ODBC;Provider=MSDASQL.1;DSN=Informix HA701;UID=informix;PWD=risc6000;DATABASE=b4c4;] &quot;
strSQL = strSQL & &quot; WHERE ttccom010200.t_net <>' ';&quot;

I'm going to create two connections and then pass field by field, data by data.

DR