Convert MDB to ADP

[Jose Perdigao]

I'm converting a MDB database to ADP and I have some problems:

1. Is it possibel to find any function in SQL server to replace Format,
left, mid functions?
2. I know, we can not use my VBA functions in SQL server.
In mdb, I have my functions to filter records open forms, queries, reports
based date and countries selected from a list box existing in a form.
Question.
How can I filter queries (views, SP), forms, reports in ADP project, based a
parameter selected from a list box or combo box?

Thanks
Jose Perdigao

 

[Robert Morley]

1. Is it possibel to find any function in SQL server to replace Format,

left, mid functions?

There's nothing in SQL Server that directly replace the Format() function.
About the closest you get is STR() to convert numbers to strings, or CONVERT
to convert a variety of arguments to other formats, including dates to
strings, etc. Neither of these are anywhere near as functional as the
Format() command was, though; you're probably going to be stuck making your
own functions for a lot of your Format() needs.

As for Left() and Right(), they're implemented exactly the same in SQL
Server, so no problems there, and Mid() is called SubString() but is
otherwise the same. I seem to remember slight differences in how they
handle Null's, empty strings, and cases where you ask for more characters
than there are; also SubString() doesn't support the two-parameter format
that Mid() does (i.e., you can't use just SubString(MyString,2) to return
everything from the second character on like you could with Mid()). You
should probably double-check the docs for SQL Server if these are a concern
for you.

2. I know, we can not use my VBA functions in SQL server.
In mdb, I have my functions to filter records open forms, queries,
reports
based date and countries selected from a list box existing in a form.
Question.
How can I filter queries (views, SP), forms, reports in ADP project, based
a
parameter selected from a list box or combo box?

If you're just using Me.Filter, you can pretty much leave your code
unchanged. The only slight changes would be things like single-quotes
around strings instead of double-quotes, no #'s around dates (I think you
can only use single-quotes there as well, and provide a formatted string as
a date...or use CAST/CONVERT). If you're using other methods, post a few
more specifics, and I or someone else will undoubtedly be able to help you
further.



Rob

 

[Jose Perdigao]

Thanks Rob,

I will try to explain what I'm using in access mdb about filter queries
I have a form where I generate reports, queries, forms and by automation I
generate excel reports.

Basicaly I have two functions, one for date, rptDate() and another to
strings rptCtry()

From a main form, when I select a date and/or coutry from a list box and
combo box, the function rptdate() receive this date and rptCtry() receive the
country.

I give an example:

SELECT B315WellTest.*
FROM B315WellTest
WHERE (((B315WellTest.dDate)=rptDate()) AND
((B315WellTest.Source)=rptCtry()));

For this proceudre all filters running well and quickly and I don't need use
the parameter Forms!formname.selectdate and etc.

I would like use a procedure similar what I am usaing in mdb. I mean, I pass
the date and contry for a table or SP to open queries, forms, reports etc.

Thanks

Jose

 

[Robert Morley]

What I would suggest, then, is to use a dynamic record source for your form.
It's fairly easy to set up. Whenever the combo box changes and you would
normally requery the form, instead, you'll want to reset the record source:

Forms!FormName (or Me).RecordSource = "SELECT B315WellTest.* FROM
B315WellTest WHERE (((B315WellTest.dDate)='" & rptDate() & "') AND
((B315WellTest.Source)=" & rptCtry() & "))"

If rptCtry() returns a string, put single quotes just inside the
double-quotes on either side; I wasn't sure. Also, dates can be tricky to
pass to SQL Server, so you may have to play around with the date format a
bit to get things working.

Your other two options (that come to mind off-hand) are to create a Stored
Procedure/Function that takes the date & country as parameters and returns
the appropriate recordset, or to send the combo box value into a table on
the back end that you can simply join into a view. There are advantages &
disadvantages to all three methods, but I would recommend the first for the
time being, as it'll easily be the fastest in terms of converting your code,
and at low volumes, nearly as fast as a stored procedure in terms of
execution time.


Rob

 

[Jose Perdigao]

Your first sugestion I think is good for open forms adn reports can I use the
same procedure?

I have many list box and combo box that is filtered with my vba functions,
can I use hte same procedure?

Thanks
Jose

 

[Robert Morley]

You'll have to take it on a case-by-case basis, but that SHOULD work well in
most situations, yes.


Rob

 

[Jose Perdigao]

Hi Rob,

I used Sp with parameters and is quicly against parametrs used in the forms.
So, I think, I need to find a solution to pass the values of my functions to
store procedures.

If you know or if yuou have any example, please tell me.

thanks

 

[Robert Morley]

I haven't really played with it much at this point, but there's a way to
specify a Stored Procedure as the record source, then you put the parameters
in the Input Parameters line. Having very little experience with it myself,
you'll have to ask someone else or look in the help files for specifics.



Rob

 

[aaron.kempf]

from what i've seen.. you dont need to use inputparameters at all ever
anywhere

if your sproc (bound to a form or report) is looking for a parameter
named PLU if you have a textbox named PLU it will automatically do the
hard work

or more importantly
have the parameters on your sproc to match the controls on your form..
so your sproc would look for a parameter named @txtPLU for example

 

[dylan touati]

What I would suggest, then, is to use a dynamic record source for your
form. It's fairly easy to set up. Whenever the combo box changes and you
would normally requery the form, instead, you'll want to reset the record
source:

Forms!FormName (or Me).RecordSource = "SELECT B315WellTest.* FROM
B315WellTest WHERE (((B315WellTest.dDate)='" & rptDate() & "') AND
((B315WellTest.Source)=" & rptCtry() & "))"

If rptCtry() returns a string, put single quotes just inside the
double-quotes on either side; I wasn't sure. Also, dates can be tricky to
pass to SQL Server, so you may have to play around with the date format a
bit to get things working.

Your other two options (that come to mind off-hand) are to create a Stored
Procedure/Function that takes the date & country as parameters and returns
the appropriate recordset, or to send the combo box value into a table on
the back end that you can simply join into a view. There are advantages &
disadvantages to all three methods, but I would recommend the first for
the time being, as it'll easily be the fastest in terms of converting your
code, and at low volumes, nearly as fast as a stored procedure in terms of
execution time.


Rob

 

[aaron.kempf]

yeah, screw the french people to.. is that the answer you're looking
for dylan?