Read only driver

[LeAnn]

I posted a question on this topic before but got no response. Perhaps I need
to be more clear.

We have a Visual Fox Pro system (with a database container) that we connect
to for data mining and reporting. We are currently using ODBC drivers for
VFP to select data - usually programmatically or through Crystal Reports. I
recently linked to the tables using the Link manager in MS Access and
discovered that I was able to update data. To satisfy our QA dept, I need to
prove I can link using a read-only driver. I found the connectionstring for
VFP:
"Driver={Microsoft Visual FoxPro
Driver};SourceType=DBC;SourceDB=c:\myvfpdb.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"

However, I don't see a read-only option. Due to the urgent nature of this
project I'd rather just use the link manager rather than code. The "machine
source" ODBC driver I created did not have a read-only option either.

Any ideas?
Thanks
LeAnn

 

[Douglas J. Steele]

Rather than linking the tables, see if you can create pass-through queries.

 

[LeAnn]

Can you elaborate more about how to do that? I've read some but have never
done it. This would require coding correct? This would delay implementation
if I had to code. I should mention that I have 2 make table queries that I
use to make local tables with fewer records which is why I have the physical
links. Can you write a pass-through query in a make-table?

 

[Douglas J. Steele]

No coding is required. To create a pass-through query, select that you want
to create a new query and pick "Design View. Don't select any tables (simply
click on the Cancel button). From the Query menu, select SQL Specific, then
Pass-Through. Display the Properties of the query, and set the ODBC Connect
Str as appropriate. Type the appropriate SQL statement into the query
itself.

You can use the pass-through query as the basis for a make-table query.

 

[LeAnn]

Ok. I tried your idea and followed the OLH regarding the connection str. I
created a System DSN in the administrator tool and then used the builder in
the ODBC Connection str property. The string that was created was:

ODBC;DSN=Test_Pass_Through;UID=;PWD=;SourceDB=L:\Test\ABC.DBC;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

ODBC - call failed.
[Microsoft][ODBC Visual FoxPro Driver]Syntax error. (#200)

For my Oracle connection, I created a System DSN and used the builder to
create the string. I received a different error:
ODBC - call failed.
[Oracle][ODBC][Ora] ORA-00936: missing expression (#936)

String:
ODBC;DSN=QALT;UID=abc;PWD=xyz;DBQ=QADB
;DBA=R;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=F;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=0;

Thanks for your help. This sounds like a great method if I can get the
strings correct.
LeAnn

 

[LeAnn]

Oh also, and I just copied the Select statement from the original make a
table query. I suspect the syntax may not be correct by just copying the
original. Here are the 2 select statements. I imagine is the the Date()
and/or Trim functions - possibly the IN() part of the statement.

Oracle Statement:
SELECT [SYS_BTS].BT_NO, [SYS_UTS].UT_NO, [SYS_ UTS].C_DATE,
[SYS_BTS].R_DATE, [SYS_UTS].CON_NO, [SYS_URS].T_NO, [SYS_URS].RSTATUS_1 INTO
LT
FROM ([SYS_UTS] INNER JOIN [SYS_URS] ON ([SYS_UTS].C_DATE =
[SYS_URS].C_DATE) AND ([SYS_UTS].UT_NO = [SYS_URS].UT_NO)) INNER JOIN
[SYS_BTS] ON ([SYS_UTS].B_NO = [SYS_BTS].B_NO) AND ([SYS_UNITS].CON_NO =
[SYS_BATCH_INPUTS].CONTRACT_ID)
WHERE ((([SYS_BTS].R_DATE)>=Date()-2) AND (([SYS_UTS].CON_NO) In
("B0049","B0050","B0088","B0185","B0188")) AND (([SYS_URS].T_NO)="BAT"));

VisualFoxPro statement:
SELECT Trim(tb.tb_id) AS ut_id, tb.ts_id, tb.rlt, tb.tb_type, run.run_date,
run.status INTO NTK
FROM tb INNER JOIN run ON tb.run_no = run.run_no
WHERE (((tb.ts_id)="OVR") AND ((tb.rlt) In ("RC","NRC")) AND ((tb.tb_type)
In ("SPL","SPM")) AND ((run.run_date)>=Date()-2) AND ((run.status)="OK")) OR
(((tb.ts_id)="OVR") AND ((tb.rlt)="RC") AND ((tb.tb_type) In ("SPL","SPM"))
AND ((run.run_date)>=Date()-2) AND ((run.status)="RR"));

Ok. I tried your idea and followed the OLH regarding the connection str. I
created a System DSN in the administrator tool and then used the builder in
the ODBC Connection str property. The string that was created was:

ODBC;DSN=Test_Pass_Through;UID=;PWD=;SourceDB=L:\Test\ABC.DBC;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

ODBC - call failed.
[Microsoft][ODBC Visual FoxPro Driver]Syntax error. (#200)

For my Oracle connection, I created a System DSN and used the builder to
create the string. I received a different error:
ODBC - call failed.
[Oracle][ODBC][Ora] ORA-00936: missing expression (#936)

String:
ODBC;DSN=QALT;UID=abc;PWD=xyz;DBQ=QADB
;DBA=R;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=F;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=0;

Thanks for your help. This sounds like a great method if I can get the
strings correct.
LeAnn

No coding is required. To create a pass-through query, select that you want
to create a new query and pick "Design View. Don't select any tables (simply
click on the Cancel button). From the Query menu, select SQL Specific, then
Pass-Through. Display the Properties of the query, and set the ODBC Connect
Str as appropriate. Type the appropriate SQL statement into the query
itself.

You can use the pass-through query as the basis for a make-table query.

 

[LeAnn]

I finally figured out the proper syntax for the Pass Through statements for
both systems. Although I made the pass through query and the make table
query is base on the pass through query.
This works perfectly! THANKS