Paramet Query on Table in SQL

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Good day,

I am importing text files in CSV via a query with masked header names on a
regular basis. Under Tables, they are linked to my database. Under SQL,
there are two places in the statement for the table name. Is there a way to
create a Parameter query to ask for the name of the table?

I have no experience with macros or VB scripts. When replying, if you can
provide me with precise code, that would be most helpful.

Thanks in advance
 
Hi Dan,

prompt for the tablename, then replace the SQL for your query, then open
the query

this code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'(e-mail address removed)
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

make a form with a listbox (or combo) for the user to pick a tablename

Name --> PickTable
RowSource --> SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.[Type] = 1
AND ((Left(MSysObjects.[Name],1))<>"~")
AND ((Left(MSysObjects.[Name],4))<>"msys"))
ORDER BY MSysObjects.Name;

'~~~~~~~~~~~~~

then, in your code behind the form to process the query:

'~~~~~~~~~~~~~
if IsNull(me.PickTable) then
'user did not pick a table
msgbox "You must specify a table",,"Missing Data"
exit sub
end if

dim strSQL as string

mTable = "[" & me.PickTable & "]"

'for instance...
strSQL = "SELECT .... " _
& " FROM " & mTable _
& " WHERE ..." _
& " ORDER BY ..."

MakeQuery strSQL, "YourQueryName"

'open the query
DoCmd.OpenQuery "YourQueryName"

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Crystal

That's amazing! Thank you!

Dan

strive4peace said:
Hi Dan,

prompt for the tablename, then replace the SQL for your query, then open
the query

this code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'(e-mail address removed)
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

make a form with a listbox (or combo) for the user to pick a tablename

Name --> PickTable
RowSource --> SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.[Type] = 1
AND ((Left(MSysObjects.[Name],1))<>"~")
AND ((Left(MSysObjects.[Name],4))<>"msys"))
ORDER BY MSysObjects.Name;

'~~~~~~~~~~~~~

then, in your code behind the form to process the query:

'~~~~~~~~~~~~~
if IsNull(me.PickTable) then
'user did not pick a table
msgbox "You must specify a table",,"Missing Data"
exit sub
end if

dim strSQL as string

mTable = "[" & me.PickTable & "]"

'for instance...
strSQL = "SELECT .... " _
& " FROM " & mTable _
& " WHERE ..." _
& " ORDER BY ..."

MakeQuery strSQL, "YourQueryName"

'open the query
DoCmd.OpenQuery "YourQueryName"

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Good day,

I am importing text files in CSV via a query with masked header names on a
regular basis. Under Tables, they are linked to my database. Under SQL,
there are two places in the statement for the table name. Is there a way to
create a Parameter query to ask for the name of the table?

I have no experience with macros or VB scripts. When replying, if you can
provide me with precise code, that would be most helpful.

Thanks in advance
Click to expand...
Click to expand...
 
you're welcome, Dan ;) happy to help

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Crystal

That's amazing! Thank you!

Dan

strive4peace said:
Hi Dan,

prompt for the tablename, then replace the SQL for your query, then open
the query

this code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'(e-mail address removed)
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

make a form with a listbox (or combo) for the user to pick a tablename

Name --> PickTable
RowSource --> SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.[Type] = 1
AND ((Left(MSysObjects.[Name],1))<>"~")
AND ((Left(MSysObjects.[Name],4))<>"msys"))
ORDER BY MSysObjects.Name;

'~~~~~~~~~~~~~

then, in your code behind the form to process the query:

'~~~~~~~~~~~~~
if IsNull(me.PickTable) then
'user did not pick a table
msgbox "You must specify a table",,"Missing Data"
exit sub
end if

dim strSQL as string

mTable = "[" & me.PickTable & "]"

'for instance...
strSQL = "SELECT .... " _
& " FROM " & mTable _
& " WHERE ..." _
& " ORDER BY ..."

MakeQuery strSQL, "YourQueryName"

'open the query
DoCmd.OpenQuery "YourQueryName"

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Good day,

I am importing text files in CSV via a query with masked header names on a
regular basis. Under Tables, they are linked to my database. Under SQL,
there are two places in the statement for the table name. Is there a way to
create a Parameter query to ask for the name of the table?

I have no experience with macros or VB scripts. When replying, if you can
provide me with precise code, that would be most helpful.

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top