Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Converting Query from .mdb to .adp

Status
Not open for further replies.

yguitana

Programmer
Aug 22, 2007
3
US
Hi,

I'm trying to convert an Access file to an Access Project (Upsize the tables to SQL Server). Some errors prevent me from upsizing my query so I am trying to copy queries from the old .mdb file to the new .adp file.

One of my query in .mdb file has this in the criteria:
[Forms]![frm_1]![txtBox].[Value]
SQL:
where (tbl_1.chn) = [Forms]![frm_1]![txtBox].[Value]

This line of code does not work in the .adp file. And the query in .adp file automatically add a " N'...' " so the code looks like:
where chn = N'[Forms]![frm_1]![txtBox].[Value]'

Does anyone have an idea of what might be wrong? Is the expression correct for query in Access Project?

Thanks - YG
 
In Access you basically just have "queries." In SQLServer, you have views, stored procedures, and functions.

Views cannot take in parameters. Stored Procedures can. Functions return a single value (although that single value could be an entire table.).

The short answer to this - build a stored procedure, not a view.

Instead of
where (tbl_1.chn) = [Forms]![frm_1]![txtBox].[Value]

you will have something like this:

create procedure myNewQuery (@myParameter as varchar(50))

Select * from my tbl_1 where chn = @myParameter
 
Thanks,

When I ran that query, it asks me to enter value for @myParameter.

How can I set value for @myParameter in the code?

I tried:
@myParameter = [Forms]![frm_1]![txtBox].[Value]
and
SET @myParameter = [Forms]![frm_1]![txtBox].[Value]

but both give me error "Incorrect syntax near 'myParameter'"


 
There are a couple of ways of doing it. Perhaps the easiest is if you are opening a form or report based off of this query, open the properties dialog of the form or report, and under the data tab, put the following in the input parameter row.

@myParameter = [Forms]![frm_1]![txtBox]

 
On the other hand, if you want to execute the query and then do something with the data using a recordset object in vba, you have the following options:

TYPE 1

Dim rst As New ADODB.Recordset
Dim cmd As New AdoDB.Command
Dim parameter1 as string
Dim parameter2 as string

parameter 1 = [Forms]![frm_1]![txtBox].[Value]
parameter 2 = something else

With cmd
.ActiveConnection = CurrentProject.BaseConnectionString
.CommandType = adCmdStoredProc
.CommandText = "my stored procedure name"
.Parameters.Refresh
.Parameters(1) = parameter1
.Parameters(2) = parameter2
Set rst = .Execute
End with

Set rst = nothing
Set cmd = nothing

TYPE 2
Dim rst As new adodb.Recordset

Set rst = CurrentProject.Connection.Execute("name of stored Procedure (" & parameter1 & ", " & parameter2 & ")")

do something with my rst if applicable

Set rst = nothing
 
I tried to insert the command in the Input Parameter of the form but for some reason it didn't work.

But this works though:

Instead of naming the parameter @myParameter, I name the parameter the same name as the form that will use it (@FormName). So my code is
Select *
From...
Where chn = @FormName

The stored procedure is called in cboTable in a form named "FormName". But for it to work properly, I have to call cboTable.Requery in the Form_Current() function.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top