How to create Automatic Parameter Query?

[Moah Scout]

Hi there?
I have a PARAMETER QUERY for selecting a certain records on user request, works fine currently; But on every new table I have to set the parametric properties manually inorder for my users to use it. I want it be automatic, just after table selection there should be a way of setting a parameter without going to the design view or whatsoever. The tables can be selected from combobox.
How can I achieve this?
any hint please!

[colintis]

If you're doing this in through form's combobox, then you'll need to change the query criteria pointed to the form's combo box ([Form]![<form name>]![<combo box name>])

If the above method is not understandable, please tell us more details on what have you done so far. Pasting code (with CODE TAG) would be better if you are not sure how its done properly

[Moah Scout]

Thanks! What you have explained works even in my db, but in my db I have two tables set as example, the user of this DB will have their own table exported from external sources; After his export he want imediately start using the resources without calling somebody from remote area for assistance on change the query criteria pointed to the form's combo box ([Form]![<form name>]![<combo box name>]), it doent sound good.
What if we build this SQL (query criteria) in a form for whatever selection of a table from combobox.
I have a Combobox shows a list of TablenamesAndQu eries in the db, (the queries doesnt have to have the Creteria). The selection criteria should be made in the forms through SQL or VBA.
How could this be achieved?
Please help

[colintis]

I'm not understanding quite well on what you tried to say...Can you tell me more about what is this external source? is it another access database table or some other database files? If so you can simply make a link table to that file source, so whenever he gets the updates he doesn't need to change things again again and again. But exclude sources such as SAP, as they do need manual export first somehow.

On the other hand with more VBA parts, you can predefine a set of SQL code, then simply ask an input of the new table name (if structure is the same), and put this name into the FROM clause to run the query at the end as a whole.

[Moah Scout]

Yeah!
Its a kind of Access Tables from another DB.
Now how to create this linktable to that source? so whenever he gets the updates he doesn't need to change things again again and again
I might get lost if exposed alone to VBA-JUNGLE bymyself.
Please help in both cases!

[colintis]

compare to other programming languages such as c++, VBA is simply a small forest.

To create a linked table, I had replied that in your other question thread, see if it works.

The VBA part I mentioned requires to work with the link table, as soon you have the new table linked (with different names), then you have everything prepared in the SQL first except the FROM clause table name. Once the user puts in the new name of the table, everything will go automatically to finish the job.

[Moah Scout]

That thread could work on exporting Exel2007.
The SQL and VBA are the ones looking for.
Let me be clear!
I have set Connection via ODBC Connection String as follows:

Sub openDB_DAO()
Dim db As DAO.Database
Dim dbName As String
Dim c As Container
Dim doc As Document
dbName = InputBox("Enter a name of an existing database:", "Database Name")
If dbName = "" Then Exit Sub
If Dir(dbName) = "" Then
MsgBox dbName & " was not found."
Exit Sub
End If
Set db = OpenDatabase(dbName)
With db
' list the names of the Container objects
For Each c In .Containers
Debug.Print c.Name & " container:" & _
c.Documents.Count
' list the document names in the specified Container
If c.Documents.Count > 0 Then
For Each doc In c.Documents
Debug.Print vbTab & doc.Name
Next doc
End If
Next c
.Close
End With
End Sub

Now how can I go around this to retrive tables from this Connections and set the parameters so that to link to a Criteria Query?

[colintis]

As from the other post we talked earlier, the processes you need is to create link table with Excel. So first I'm point out the thing you need to do, I'll be back to post some example codes later.

1. Ask user to give input of their excel file
2. Check if this worksheet name exist in the link table, if yes remove it
3. create a new link table with that user's excel file
4. finish the job.

[Moah Scout]

Yes! We are heading the same direction!

[Moah Scout]

It can be database or just excel sheet, all have the same meaning for me!
If you give one I will finish the next