Combo box connected to an ODBC source directly

[Seth Schrock]

I'm trying to make it so that I can have a combo box that lists the tables available in a SQL database based on the server name and database name that I have entered into textboxes. I don't want to create a passthrough querydef. I read online that I might be able to set a DAO Recordset as the row source of the combobox. The problem is that I don't know how to provide a connection string to a DAO Recordset.

I have gotten this to work using a passthrough querydef. I'm just trying to do away with needing the passthrough querydef.

[twinnyfo]

Seth,

Up front, I don't know if this will work, as I don't use ODBC--all my tables are linked Access DBs. But, have you tried accessing the Table names in MSysObjects? You will have to turn on viewing system objects, but it might provide your connected Table names, and then go from there...

Just a thought, as I have used this as a source for my Switchboard listing Form and Report names.

[Seth Schrock]

For the tables to be in MSysObjects, it has to be a table that has already been imported. I'm looking for all tables in the source database so that I can add them from a drop-down box.

[twinnyfo]

Ahhh! Now I get it. There's gotta be a way to open the connection ans see, I would think. Sorry I can't be of more assistance on this one.....

[NeoPa]

May I suggest you post this question in the relevant Back-End forum Seth. If I understand you correctly that would be for MS SQL Server, but whichever, that would be where the relevant expertise would most likely be found I expect.

[Seth Schrock]

I know the SQL Server side of it (using the system Views to get the information, etc.), but I don't know the Access side of using a passthrough query as the row source of the combobox without having a querydef.

[Rabbit]

Is there a reason it has to be done without a querydef?

[Seth Schrock]

Just to make it easier to move it between databases. There are also other system views that I would like to get access to (to get primary key information) so I was hoping to not have to have a querydef for each list that I needed.

If it isn't possible without a querydef, I'll just make my code create it so that I don't have to copy it for each database project that I work on.

[Rabbit]

You can try to use the IN clause but I don't know if it works with SQL Server. http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[Seth Schrock]

The SQL magician does it again! I am able to query SQL Server: http://support.microsoft.com/kb/101375/EN-US. My only problem now is that it isn't liking my join between the system views, telling me JOIN expression not supported. I'm terrible at typing out joins, so maybe I did it wrong. Here is what I have

SELECT COLUMN_NAME, T.TABLE_NAME, T.TABLE_SCHEMA
FROM [INFORMATION_SCHEMA.TABLES] AS T LEFT JOIN
([INFORMATION_SCHEMA.TABLE_CONSTRAINTS] AS TC INNER JOIN
[INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE] AS CCU
ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME)
ON T.TABLE_NAME = TC.TABLE_NAME
IN "" "ODBC;DRIVER=SQL Server;SERVER=sqlbench;DATABASE=ePayments;Trusted_Connection=Yes;"
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

I was able to successfully join two views, but I can't get the third. Is there a limit on the number of joins with this type of query?

Your link also got me searching online and I found How to Access Multiple Databases in an SQL Query in VB 3.0 which tells about another way to connect. I like the IN method for single database connections as it requires less typing. I did try the second method to see if it would work for me, but no difference.

[Seth Schrock]

Just an FYI for anyone else trying this, for some reason Access removes the square brackets around the table names using the method shown in the previous post. You have to use the following method for it to work:

SELECT CCU.COLUMN_NAME,TC.TABLE_NAME, TC.TABLE_SCHEMA
FROM [ODBC;DRIVER=SQL Server;SERVER=sqlbench;DATABASE=ePayments;Trusted_Connection=Yes;].[INFORMATION_SCHEMA.TABLE_CONSTRAINTS] AS TC
INNER JOIN  [ODBC;DRIVER=SQL Server;SERVER=sqlbench;DATABASE=ePayments;Trusted_Connection=Yes;].[INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE] AS CCU
ON TC.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

Very annoying, but at least it works.

[Rabbit]

So it sounds like you were able to get it to work with the other method?

If not, sorry, I don't know if there are limits to the amount of joins you can have. I have not used either method before, I only know of it in passing.

[Seth Schrock]

I was only able to get two tables to join using both methods. However, the second method is required to work as the row source of a combo box.

I'll keep playing with it and see if I can come up with something. If I get it to work, I'll post it here. Thanks again Rabbit.

[NeoPa]

Both Access (Jet) and SQL Server (T-SQL) allow joins of greter complexity than two sources. T-SQL is more robust, but in Jet you can put parentheses around a joined pair of sources to be treated as a single logical source. There are restrictions still, but mainly that you can never use INNER JOIN between any two logical sources where either includes any OUTER JOINs.

[Seth Schrock]

Well, within SQL Server I'm able to do the left join and then the inner join. In Access it is making me change the left join to an inner join. I also have to add parentheses around the first join differently from how I had tried before. So here is my three table join that is working in the method that works for a combobox RowSource.

SELECT CCU.COLUMN_NAME,T.TABLE_NAME, T.TABLE_SCHEMA
FROM ([ODBC;DRIVER=SQL Server;SERVER=sqlbench;DATABASE=ePayments;Trusted_Connection=Yes;].[INFORMATION_SCHEMA.TABLES] AS T
INNER JOIN [ODBC;DRIVER=SQL Server;SERVER=sqlbench;DATABASE=ePayments;Trusted_Connection=Yes;].[INFORMATION_SCHEMA.TABLE_CONSTRAINTS] AS TC
ON T.TABLE_NAME = TC.TABLE_NAME)
INNER JOIN  [ODBC;DRIVER=SQL Server;SERVER=sqlbench;DATABASE=ePayments;Trusted_Connection=Yes;].[INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE] AS CCU
ON TC.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'