ODBC error on pass-through query (1 Viewer)

[CS45]

Hi all,
I’m trying to automate a query that requires a security login and password through ODBC. The internet said to use a pass-through query and showed me how to do the ODBC Connect Str in the Property Sheet. I saved the password to the string, but when I run it, it returns an error saying “identifier is too long.”

Any resolutions? Thanks!

 

[Isaac]

It could be a string enclosed with quotes that you forgot to enclose with the ending quote or bracket (especially if it's an object name). I would examine your SQL itself for the most likely culprit being an error there. Post the SQL if you can.

 

[CS45]

For security reasons I cannot post it. I created the query in Design View then converted it to SQL view so I wouldn’t think it would convert incorrectly.....I’m very unfamiliar with SQL code so I’m not sure.

 

[Isaac]

So you created it in Design View in Access, then viewed the SQL view, and used that exact SQL for your pass-through query to your database server? For what it's worth, if it's any of the major paid RDBMS like Oracle or SQL Server....I would be surprised if the Access-generated SQL just so happened to be perfectly acceptable, in its entirety, to the RDBMS you are passing it to. Unless it is a very trivial query with very, very little SQL.

Normally one approaches a pass-through query as: Write the SQL from scratch, using the syntax/wrapper from the system in question. For example, you'd write a pass through query destined for SQL Server in T-SQL, and one destined for Oracle with PL-SQL rules in mind.
Now there's nothing wrong with using Access-generated SQL as a starting point for that exercise, IF it helps you and IF you know what changes will be needed.

That's about the extent of my thoughts, so far, without being able to see your SQL and know where it's headed.

Edit ... I should have added, you probably already do this, but if not certainly try running your pass through query's SQL in the RDBMS's typical development tool (i.e. if it's Oracle try running your SQL in SQL Developer or Toad, if SQL Server, SSMS), and see if it works there.

 

[CS45]

Ok, I’ve made my code compliant with Oracle SQL. Now, when I run the code it says that the table or view doesn’t exist. In my select/from clause I’m using the MS Access table I’m wanting to pull from, similarly to how you choose “table” in a select query...any thoughts?

 

[Isaac]

You need to specify the Oracle (remote) table, named precisely .... with any required prefixes/notation .... Not the Access-named table. When a table is linked from a remote database into Access, the named of the linked table in Access can be made totally different than the name of the actual table on the remote database...and quite often is.

 

[Isaac]

Remember the goal of the pass through query is to provide a block of SQL to the destination database, which will have nothing whatsoever to do with the Access database.

One thing to think about is 'default database'. I'm not intimately familiar with Oracle SQL, but here are some thoughts:
Example: you might be working in SQL Developer or Toad and you may have a connection that specifies the database you are connecting to. In that context, your sql may only need to reference the name of a table. But in your access pass through query, IF you don't have the initial database as part of the connect string, then you may need to adjust the notation of the table name you are selecting from to include the database. Make sure you are comparing apples with apples. You may need to experiment with different prefixes/notations/identifiers depending on your connect string.

 

[CS45]

Ok, interesting. So in addition to the table itself, all the fields listed in the SQL must be the names of fields in the Oracle db table, not the access table. I’ll see what I can do, thanks.

 

[Isaac]

Yes, correct. the PT sql will be totally without reference as if Access does not exist (and there is no need to even link the table in Access, hypothetically).

 

[Minty]

there is no need to even link the table in Access, hypothetically).

This is a massive advantage when you want to obfuscate what is happening from the end-user.
You can use this to check login names etc without having to have the table in Access at all.

 

[CS45]

With the linked table in Access, is there any way to look at what DB table Access is pulling from? I didn’t write the Access table code so I don’t know where it pulled from inside the DB

 

[Isaac]

Yes ... go to the vba project, go to the immediate window (view, immediate window), and type:
?currentdb.tabledefs("TableName").Connect [then hit Enter]

To me that's the quickest way

 

[CS45]

It just returns a bunch of gibberish...is it possible that it’s linked to more than one table in the DB?

 

[Isaac]

By a bunch of gibberish, what exactly do you mean. Like weird characters? I've only always seen it return something meaningful, but I guess "meaningful" is in the eye of the beholder.

I would assume it's linked to a single table, view or ...something like that

 

[CS45]

I don’t understand any of the gibberish, it’s just a bunch of things equaling random letters and numbers

 

[Isaac]

Hmm. Not sure what to tell you on that. Maybe someone else has seen what you are describing.
Another thing you might try is:

?currentdb.tabledefs("TableName").SourceTableName

Maybe you can post the result and I can look at it.

 

[CS45]

That worked much better!

 

[Isaac]

Oh! Very glad to hear that. Very curious about the connect string being gibberish, but who knows. It's been years since I had an Oracle linked table and at the time I don't think I examined connect strings ever.

 

[CS45]

I think it may have to do with the ODBC driver but I’m not sure. Anyway, now it is saying that all my identifiers are invalid. Is there a similar way to see what identifiers the DB table uses?

 

[CS45]

View* not table as it turns out