Reserve Word Hell

[prley]

My delimma is that I'm trying to build a querydef on the fly in vba to run as odbc to connection to oracle. Our oracle data base has a table named case which is casing me problems in connecting. Here is the query.

"Select case.case_num, case.Case_defendant & _
"From [Prod.case_public_party_rel], [Prod.Case] " & _
"Where case_public_party_rel.party_id = 99261 " & _ and case.case_id = case_public_party_rel.case_id "

Case_id is a key in the Case table and a foreign key in the case_public_party_rel table.

the query def looks like this after the create statement:

SELECT case.case_num, case.Case_defendant
FROM Prod.case_public_party_rel, Prod.[case]
WHERE party_id = 99261 and case.case_id = case_public_party_rel.case_id

The extra bracket around case is giving me the headache Prod.[case]

anyone have similar issues?

 

[ajetrumpet]

iw ould suggest changing the name of this, if it's not too much trouble. lol. =)

 

[prley]

iw ould suggest changing the name of this, if it's not too much trouble. lol. =)

That's my problem it's not an option. Thanks for the reply

 

[raskew]

Instead of Reserve Word Hell, I'd suggest Table/Field Naming Hell.

Don't know how you could make it anymore confusing/error prone with all the various references to Case.

Example

"Where case_public_party_rel.party_id = 99261 " & _ and case.case_id = case_public_party_rel.case_id "

Don't know how anyone can look at that without their vision going blurry.

Bob

Added: 'Case', incidentally, is not a reserved word.

 

[RuralGuy]

Added: 'Case', incidentally, is not a reserved word.

Sorry Bob,
CASE JET reserved (kb248738);SQL Server reserved;ODBC (kb125948)
My reference is Allen Browne's list.

 

[raskew]

Hi Allan -

Thanks for that (I think).

My mistake (maybe). My sources were:
Microsoft:
http://support.microsoft.com/kb/286335
http://support.microsoft.com/kb/109312

We may need to get them hooked-up with Allen Browne.

Best wishes - Bob

 

[prley]

And besides go easy on my me. Can't help it if the consulting firm for our 6 million dollar system are naming idiots. I could go on forever about their mistakes. Thanks for the help criticism is not very helpful since still need to connect.

 

[Banana]

For one thing, while this may be valid to do a <Schema Name>.<Database Owner>.<TableName> in Oracle or SQL Server, this is not valid in Access. It sounds to me you really need a passthrough query, not a linked query, which will also sidestep all that reserved word because Jet will not try to parse it for you anymore.

I've not done it before but I imagine that if I were to do it on fly, the code should be:

With CurrentDb.QueryDef("zz_tmpPTQ")
   .Connect = <Full connection string>
   .SQL = <Full SQL statement>
   Set rs = .OpenRecordset, dbSnapshot
End With

The only thing I would want to double check is when you can set the Connect property of the QueryDef, and verify that Jet understand this is a passthrough query.

Go and experiment with it a bit and see if it takes you further.

If you were to just link the table, you would find that Access would rename Oracle's table "Prod.case_public_party_rel" to "Prod_case_public_party_rel", though in the SourceTableName property, which is the actual name Jet will use in its querying the backends, the original name will be preserved. Therefore, in a linked query, you would refer the Access's alias of Prod_case_public_party_rel instead of the actual name Prod.case_public_party_rel.

I hope that helps.

 

[prley]

For one thing, while this may be valid to do a <Schema Name>.<Database Owner>.<TableName> in Oracle or SQL Server, this is not valid in Access. It sounds to me you really need a passthrough query, not a linked query, which will also sidestep all that reserved word because Jet will not try to parse it for you anymore.

I've not done it before but I imagine that if I were to do it on fly, the code should be:

With CurrentDb.QueryDef("zz_tmpPTQ")
   .Connect = <Full connection string>
   .SQL = <Full SQL statement>
   Set rs = .OpenRecordset, dbSnapshot
End With

The only thing I would want to double check is when you can set the Connect property of the QueryDef, and verify that Jet understand this is a passthrough query.

Go and experiment with it a bit and see if it takes you further.

If you were to just link the table, you would find that Access would rename Oracle's table "Prod.case_public_party_rel" to "Prod_case_public_party_rel", though in the SourceTableName property, which is the actual name Jet will use in its querying the backends, the original name will be preserved. Therefore, in a linked query, you would refer the Access's alias of Prod_case_public_party_rel instead of the actual name Prod.case_public_party_rel.

I hope that helps.

Thanks for the info. Yes it does help. But my problem isn't connecting it's building the query def. I am using the connection property to connect and all is well if I change the qerydef to have the right syntax. I can connect throuh a link table but I think that requires the user to log into the database which I don't want to do. I wanted to create the query def set the connection string then delete it from the database after I'm done with it. I might try the qerydef.sql command a little more. I don't think I've tried all the options there. Thanks again.

 

[Banana]

Two things.

1) You can just create one querydef, call it something like zz_tmpPDQ or something like that to indicate that it is for the developer's use and not a "user's query" per se, which would always hold the connection string for you. You can alter SQL on the fly when you need it using the similar syntax I gave you above. It also save you from the trouble of creating & deleting the querydef.

2) To clarify, just because it's linked, does not mean you have to be prompted for login every time you try to access a linked table. You can 1) save the login credentials inside the Connect property of the linked table (understand that this is a security risk. Not a big deal if you trust your users and it's never taken out of your office's LAN for instance.) or 2) fill in the credentials programmatically so the user never sees the prompt. The specifics of how you fill it is entirely up to you, whether it is from reading a text file or presenting your custom log in form asking only for username & password and supplying the rest.

I hope that helps.

 

[prley]

Thanks Banana the .sql property worked flawlessly. I think I will keep the one quedef through the whole operation (less code to write). That works best then have to deal with the items in #2. Thanks again.

 

[Banana]

Cool. Thanks for reporting back.