Run query on user defined table name

[Dave_epic]

Hi

I import a table into access using transfer text with the code below. The code asks the user for the name of the table at runtime. I then want to run a query on the table with the same code but how can I do this if the table name is defined by the user.
Thanks

 [/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Option Compare Database[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman] [/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Private Sub import_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]On Error GoTo Err_Import_File_Click[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Dim strFile_Path As String[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Dim strTable As String[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]'Prompt user for file path[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]strFile_Path = InputBox("Please enter file path and file name")[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]'Prompt user for name of table to create for imported data[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]strTable = InputBox("Please enter name of new table")[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]DoCmd.TransferText acImportDelim, "spec_import", _[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]    strTable, strFile_Path[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]DoCmd.OpenQuery "query1", acViewNormal[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Exit_Import_File_Click:[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Exit Sub[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Err_Import_File_Click:[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]If Err.Number = 3011 Then[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path"[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Else[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]MsgBox Err.Description[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]End If[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Resume Exit_Import_File_Click[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]End Sub[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]

 

[DCrake]

For a start you cannot use DoCmd.Openquery on a select query. Ie non action type query. This only applies to Insert, Delete, Update, etc type queries.

The only way to achieve this is to create a form that is of datasheet view type and set the recordsource to the name of the table the user has nominated. You would have to delcare the strTable as a public variable and on the onload event of the form set the RecordSource to strTable.

Eg:
Me.RecordSource = strTable

 

[Dave_epic]

Thanks.

I should have mentioned it was a make table query I wanted to run. So I have decided to go with running the query first and renaming as user defined after, it seems simpler.

 Option Compare Database

Private Sub import_Click()
On Error GoTo Err_Import_File_Click
Dim strFile_Path As String
Dim strTable As String
'Prompt user for file path
strFile_Path = InputBox("Please enter file path")
 
DoCmd.TransferText acImportDelim, "comments_import", _
    "new_table", strFile_Path
 
DoCmd.OpenQuery "query1", acViewNormal

'Prompt user for name of table to create for imported data
strTable = InputBox("Please enter name of new table")
 
DoCmd.CopyObject , strTable, acTable, "new_table1"
DoCmd.DeleteObject acTable, "new_table"
DoCmd.DeleteObject acTable, "new_table1"
 

    
Exit_Import_File_Click:
Exit Sub
    

Err_Import_File_Click:
If Err.Number = 3011 Then
MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path"
Else
MsgBox Err.Description
End If
Resume Exit_Import_File_Click
 
End Sub

 

[gemma-the-husky]

For a start you cannot use DoCmd.Openquery on a select query. Ie non action type query. This only applies to Insert, Delete, Update, etc type queries.

The only way to achieve this is to create a form that is of datasheet view type and set the recordsource to the name of the table the user has nominated. You would have to delcare the strTable as a public variable and on the onload event of the form set the RecordSource to strTable.

Eg:
Me.RecordSource = strTable

Dave

why do you think you cant open a select query with docmd.openquery?

I often do this to quickly show some info, when I cant be bothered opening a form. You can even open readonly to prevent inadvertent edits.