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

[colintis]

I'll just simply post the major part you need, you'll sort the rest which it won't be too difficult. Make sure you place them as a module in vba

1. So we're asking for an user input for a file.

Public strFilePath As String 'This will first from your user input things

Function XlsPath() As String

    Dim dlg As FileDialog
    Dim VrtSelected As Variant
    
    'Set up the File Dialog for FILE picker
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)

    With dlg
    
        'Allow user to make multiple selections in dialog box.
        .AllowMultiSelect = False
        
        'Set the title of the dialog box.
        .Title = "Please Select Database File"
        
        'Clear out the current filters, and add our own.
        .Filters.Clear
        .Filters.Add "Excel Workbooks 2007", "*.xlsx"

        'Set location the dialog will show to database's root directory
        .InitialFileName = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
        .InitialView = msoFileDialogViewList
        
        'Show the dialog box. If the .Show method returns True, the
        'user picked at least one file. If the .Show method returns
        'False, the user clicked Cancel.
        If .Show = True Then

            'Loop through each file selected and add it to the list box.
            For Each VrtSelected In .SelectedItems
                XlsPath = VrtSelected
            Next

        End If
        
    End With
    
    Set dlg = Nothing
    
End Function

From here, you'll need to think of some ways to extract the file name for step 2 from XlsPath

2. Check if the name exists already in the table list

Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
On Error Resume Next
Set td = db.TableDefs(strFileName)
If Err.Number = 0 Then
    MsgBox "Table found."
    db.TableDef.Delete strFileName
    Err.Clear
Else
    MsgBox "Table not found."
    Err.Clear
End If

Set db = nothing

3. Create new linked table that user selected earlier

Dim db As Database
Dim linktbldef As TableDef
Dim sourceTable As String

sourceTable = tblName & "$"   'Get the worksheet name you want to link

    Set db = CurrentDb
    Set linktbldef = db.CreateTableDef("tmptable")  'create temporary table definition
    linktbldef.Connect = "Excel 12.0;HDR=YES;IMEX=2;DATABASE=" & FilePath                  'set the connection string for Excel 2007
    linktbldef.SourceTableName = sourceTable        'attach the source table
    db.TableDefs.Append linktbldef                  'add the table definition to the group
    db.TableDefs.Refresh                            'refresh the tabledefinitions
    
    linktbldef.Name = sourceTable                   'rename the tmptable to original source table name

4. From here then on you'll on your own trying to get the stuffs working. Good luck.

[Moah Scout]

If you run the first Code, it gives error and point to the dlg As FileDialog:
User defined-Type not Defined.
I couldnt go around that error!

[colintis]

Just forgot, you need to reference the Microsoft Office 12.0 Object Library

In the VBA menu, tool-->reference, then find office 12.0 object library

You also need other libraries in the reference to get all your stuffs work. Look around and see.

[Moah Scout]

Well it works after setting the Microsoft Office 14 Object Library, but it just open the dialogbox with .*XLSX* file format but it cannot allow me to select the desired sheet in a Spreadsheet and yet nothing happens after Click-OK; Only dialogbox dissapears. Assume it opens Successfully!
My question is How to set the newly opened .xlsx document to my query criteria without entering them mannualy?

[colintis]

There are many more to complete, as I told you earlier those are the major parts, not 100% of the whole picture, and as for that it is then your responsible to figure it out. This is the furthest hint I can give you, unless you actually looking for someone to do the work for you.

If you look carefully to the codes, you'll notice why it just look for .xlsx files, you can then put other more excel extension files to it if you like.

And look clearly on how the function is defined, the function itself actually return the path of the file, this is where you work it out on how it can be store as well.

After that, it is another part of the code, which you will be read through the excel file, and list out the worksheets available in it or look for a specific one you like. Below will be the last bit of code about listing worksheets from the file, make yourself work around with the module function reading excel file.

Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlwb As Excel.Workbook

Set xl = CreateObject("Excel.Application")
Set xlwb = GetObject(FilePath)

For Each xlsht In xlwb.Worksheets
    'lstShts is a listbox item on a form
    lstShts.AddItem (xlsht.Name)
Next xlsht