Linking to a non valid table

[matty1404]

Is it possible to link to a table that has periods in the name?? It says it cant, but is there a way around it?

 

[Remou]

I think you are more likely to get an answer if you say where the table is that you want to link to.

 

[matty1404]

I have a table in an as400 database. I want to link it to access so i can pull certain records off it and put it into a report. The table name is bl.table. It says that it is not a valid name because of the period.

 

[Remou]

I can only guess, however someone who knows may come along. Until then, have you tried linking to a view / query on bl.table?

 

[matty1404]

How do you link to a view/query?

 

[dhookom]

Use a pass-through query to get your data/records from the AS400. This allows you to use the exact SQL syntax you would use in the AS400 environment.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[traingamer]

See also previous discussion at thread181-1333673

 

[matty1404]

I dont know how to set up a pass/through query. We dont run SQL.

 

[Golom]

We dont run SQL.

Yes you do. Access retrieves and manipulates data ONLY with SQL.

On a new query, select "Query / SQL Specific / Pass-Through" from the menu and then type the code for your pass-through query.

 

[matty1404]

I dont know the syntax. I connect to the database and it says it failed. Do you have any sample code for me to try?

 

[lespaul]

try something simple like:

SELECT * FROM Library.bl.TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[matty1404]

Is it possible to slect from two different libraries?

 

[dhookom]

A pass-through supports any syntax available in your server database. You can create as many pass-throughs as you want.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Sure, use JOINS:

SELECT * FROM
Library.Bl.TableName
INNER JOIN OtherLibrary.OtherTable ON OtherTable.SomeField = Bl.TableName.Field

You may want to read the Understanding Join article linked below.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[matty1404]

I got it figured out now. Thanks a lot