how to access data in a query

[Guest]

I don't know how to set the source property on a query? or how does
the query know what db to query? It now says (current) so if you have another
db open then it queries the current db when you open the query in datashett
view?
I'm assuming this is what is meant by the query source property?
I have an excel spreadsheet that I would like to
populate my db so I can test the query. I tried setting the
source property which says (current) to [tables.tblTest] on the query
just to get some data in it says the name is invalid. (I imported from an
Excel spreadsheet some test data into a test table so how would I get that
data into the query.

tia,

How do you set the source property so the query
would work on a table or how do I set the query to get data from the
Access db I want to query? If you go to external data and import then
shouldn't it set the property in the

 

[Guest]

The SourceDatabase property of a query provides a means of querying a table
in an external database without linking to the table that database. It
corresponds to the IN clause in SQL and is a string comprising the full path
to the external database file, which doesn't have to be open. It is not very
often used in my experience and I suspect that on the relatively rare
occasions when it is used most developers would write it in SQL rather than
via the properties sheet. I must confess that until I read your post I
wasn't even aware it could be done via the properties sheet.

Most of the time when accessing data in an external database a link would be
created in the current database to the table via the File | Get External Data
| Link menu item on the main database menu bar. A link can also be created
to an Excel spreadsheet or various other file types. Once a link has been
created to an external table in this way it can be queried just like a local
table without having to concern yourself with the SourceDatabase property.

From what you say you seem to have already imported the data from your Excel
worksheet into a table name tblTest, right? If so then you can simply query
that table without having to do anything to the SourceDatabase property. To
add a table from the current database to a query in design view, whether it’s
a local or linked table, select Show Table from the Query menu, select the
table in question from the list and click Add. Unlike a linked table the
table containing the imported data won't reflect any changes made to the data
in the Excel worksheet since you imported it.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

I don't know how to set the source property on a query?

Ordinarily you wouldn't.

or how does
the query know what db to query?

The Query has a FROM clause which names the Table or Tables which it will
report. These might be local tables, or linked tables, or you might have an IN
("C:\somepath\databasename.mdb") clause in the query if it's in another
database.

It now says (current) so if you have another
db open then it queries the current db when you open the query in datashett
view?

The (current) just means that the query references a table in the currently
open database. This might - again - be a local table, or a linked one
connected by using File... Get External Data... Link.

I have an excel spreadsheet that I would like to
populate my db so I can test the query. I tried setting the
source property which says (current) to [tables.tblTest] on the query
just to get some data in it says the name is invalid. (I imported from an
Excel spreadsheet some test data into a test table so how would I get that
data into the query.

That's not the best way to do it. Instead, open the Tables window, and select
File... Get External Data... Link from the menu. Choose Excel from the "files
of type" option, and link to your workbook.

John W. Vinson [MVP]