ODBC Schema

Question

I have a BBj ODBC client set up to access data. On my old XP / Access 2003 system when I connected it shows just the table names in the list:

ACCOUNTS_PAYABLE

ACCOUNTS_RECEIVABLE

On my new Windows 10 / Access 365 system it now has whatever user I am connecting with as a prefix in the table name

username.ACCOUNTS_PAYABLE

username.ACCOUNTS_RECEIVABLE

how do I get the ODBC connection to drop the login as a prefix in the table names. The BBj ODBC client only has fields for Server, Port, User Name, Password and database.

Answer 1

As both of us have noted, the schema is undoubtedly part of the linked table's definition. The fact that it is not SHOWN in the interface is most likely an artifact of the original linking method used for your A2003 version. Here, for example, the schema is dbo, which is the default schema for SQL Server.

Access displays the values from the Name field, but it definitely knows that the name actually is the one stored in the ForeignName field.

Answer 2

Do this:

Ctrl+G to open the Immediate window

?currentdb.TableDefs("TomTest").Name, currentdb.TableDefs("TomTest").SourceTableName

TomTest dbo.TomTest

Of course you replace TomTest with your table name.

I bet that in A2003 you see the Name property being short, and the SourceTableName having the prefix, just like I wrote in first instance.

Answer 3

BBj is the ODBC Client for the Basis Business Basic Programming language that the customers application is written in. I'll have to see what is different in the Access 2003 / Basis ODBC client which is relatively old, but does not have the schema prefix so it shows only ACCOUNTS_PAYABLE.

Answer 4

"Dropping" schema names isn't possible. The schema and table names are both required to properly identify tables in SQL server(or other RDBMS). Basically, it is possible to create multiple schemas in the database and name DIFFERENT tables, one in each of those schemas with the same name. Only the schema name provides the required disambiguation. You could have, for example username1.Accounts_Payable and username2.Accounts_Payable. You have to refer to the schemas to know which is which.

However, it is possible to alias the linked tables in your Access accdb in any way you want (provided of course you follow the rules for names).

That means you can simply click on the linked tables in the navigation pane and rename them, leaving off the schema:

E.g. the linked table called username1.ACCOUNTS_PAYABLE can be renamed ACCOUNTS_PAYABLE_Joe and and username2.Accounts_Payable could be renamed Accounts_Payable_Mary.

With an alias name for your linked tables, Access and the ODBC connector know that the "real" table name is username.ACCOUNTS_PAYABLE, but treats it as ACCOUNTS_PAYABLE locally.

I'm afraid I don't recognize the acronym you use for this linked DB. What does BBj refer to?

Answer 5

I don't know BBj ODBC but Microsoft SQL Server linked tables do something similar:

schema_tablename

Often schema is "dbo".

If we don't want that, we can rename the tables to tablename.

This is great: under the hood it's still the full name, but we can work with queries without it:

select * from tablename;