Paramet Query on Table in SQL

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
 
S

strive4peace

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
*
 
D

Dan

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...
 
S

strive4peace

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table Names in SQL Script 4
Linked SQL Table - Can't Read 2
Access stripping decimal places in query 1
Dynamically change Query SQL 2
SQL question 7
Insert Into a SQL table via Access 2007 8
Date Restrictor no longer works when linking to SQL Server table 5
VB Access Table creation from a SQL Pass-Through Query 2

Top