ODBC Database Tables

[demicay2669]

I have Linked a few ODBC Databases to my database in the Tables objects. How do I make them usable for queries or as record source for my form? I tried to create a query but the ODBC databases do not show up in the Table options.

Database is attached. Any help would be much appreciated.
Thanks in advance.

 

[demicay2669]

Yes the way my tables are setup is intentional. It was the best way to get my form to work the way I want. I do know about and understand normalization.

 

[namliam]

You should be able to make use of the Linked tables as a rowsource....

If you want to create queries on the ODBC link, you can change a query to a "Pass-Through" query

 

[demicay2669]

How do I go about that? Cause when I try to do create a query the ODBCs do not show in the tables list.

 

[namliam]

what kind of query do you want to create ?
1) An access query that uses the ODBC tables
that should just be possible as the DBO_PART, dbo_PartPlant, etc tables can just be added to the query no problem

2) An ODBC query
in the property sheet of the ODBC query you need to build the ODBC COnnect String.
Once done you have to hand write the SQL you want to execute on the target database, no pretty GUI or anything to help you just plain SQL

 

[demicay2669]

I guess I should make sure this is clear, that I am doing a web database.
I need a query for each of the ODBC tables to use to pull specific columns to fill one or two fields on the form from a combobox. My ODBCs have way more info than I need for my form. All I really need for example from dbo_Part is the PartNum and PartDescription columns.
And I'm not too good at coding, but usually I can get by with some help in the right direction.

 

[namliam]

so go for option 2?

 

[demicay2669]

Thank you Mailman. Your pass through query idea helped. For some reason the ODBCs don't show up if your trying to do a Web Query, but they do if you do a Query Design as it says to do when I looked into Pass through queries.

 

[demicay2669]

This is a web database i am building.
I've tried to create a Pass-Through Query for my ODBC tables, but I recieved an error. Which I have attached as well as my db.

I'm also not very good at writing code, so thats probably lacking as well. I could use a little help there. This is all i have so far.

SELECT dbo_vCustomer.CustID, dbo_vCustomer.Name
FROM dbo_vCustomer;

The objective is for a combo box to query the ODBC table to populate the information a user will need to fill in the combo box field. For instance the cmbCustName and CustID to fill from dbo_vCustomer.

I found my pass through query help from. http://support.microsoft.com/kb/303968

 

[namliam]

When your doing a query ODBC into the sql database you cant have the DBO_ prefix...
DBO is the default "schema" instead change it to "DBO."

Or dont use the ODBC query and use a normal one where you can use the DBO_vCustomer table.

 

[demicay2669]

So your saying change the table name to "dbo." instead of "dbo_" And then make the change to the code as well?

 

[demicay2669]

What kind of Query do I need to do so I can use it as the Row Source for a field? So far I have been able to create a Query that I can even see in the Show Table pop up when I select the query builder button on the Row Source.

 

[namliam]

any query should do

 

[demicay2669]

None of the queries I have made show in the Show Table pop up. And I still can't get a Pass-Through query to run.

 

[demicay2669]

I was able to get the Pass-Through Query to run using the code below.

SELECT [CustID], [Name]
FROM [dbo].[vCustomer];

But I am still unable to see the queries as an option in the Row source Show Table pop up.

 

[demicay2669]

I was able to come to a work around for the ODBC queries. They are only usable on Client Forms not Web Forms. So I created a client form in the same format as my web form. The only catch is that for someone to use the forms full function they have to click "Options" and "Open In Access" while on SharePoint. So I set the Current Database Options to open the client form as the Display form.
Sorry I didn't write this sooner.