Converting Query from .mdb to .adp

[yguitana]

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

 

[belovedcej]

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

 

[yguitana]

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'"

 

[belovedcej]

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]

 

[belovedcej]

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

 

[yguitana]

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.