From .mdb to .adp database

[Nesta]

Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a textbox, in a
form.
In my mdb database, the syntax was like : [Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and obviously does
not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Norman Yuan]

ADP file, not like MDB, does not contains query. The queries you see in ADP
file are SQL Server objects (Views, Stored Procedures and UDFs...). When
converting, the queries in MDB may or may not be converted to corresponding
SQL Server objects, depending on what is in the mdb queries. In your case,
your query was conerted as stored procedure, which sits in SQL Server back
end, and has no knowledge of your ADP front end (and it should not be tied
to any specific front end at all), so the conversion smartly enough to a
sptored procedure and converted the two required inputs as input parameters.
Now, when you call this stored procedure in an front end application, such
as ADP file using it as a form's data source, you need to get user input and
pass the input as the stored procedure's parameters.

If you already have an Access application done in MDB file, the better
approach would be to only convert data back end to SQL Server, and still use
your MDB front end, with possible minor modification. After all, ADP type of
application is dying.

 

[Nesta]

Hi Norman,

Thank you very much for your response.
I understand how things work better now.
Because i'm used to work with mdb files, it was difficult for me to
understand that ADP files are just an interface that is not directly
connected with databases, like it is in mdb (for example, the queries).
The reason why i got interested into ADP files is that i'm trying to find
the best way (with less changes to do ans without great changes in
interfaces) to convert my mdb into SQL.
But, as you say ADP is dying, maybe this way is wrong.
I know i can convert my tables and stuff into SQL and still use mdb
interface (by changing the link of the bases), but if i want to
redistribuate my application, i will have to create the dbo connection on
every computers, isn't it ?
Do you have a better solution ?

Thanks by advance.

Nesta

ADP file, not like MDB, does not contains query. The queries you see in
ADP file are SQL Server objects (Views, Stored Procedures and UDFs...).
When converting, the queries in MDB may or may not be converted to
corresponding SQL Server objects, depending on what is in the mdb queries.
In your case, your query was conerted as stored procedure, which sits in
SQL Server back end, and has no knowledge of your ADP front end (and it
should not be tied to any specific front end at all), so the conversion
smartly enough to a sptored procedure and converted the two required
inputs as input parameters. Now, when you call this stored procedure in an
front end application, such as ADP file using it as a form's data source,
you need to get user input and pass the input as the stored procedure's
parameters.

If you already have an Access application done in MDB file, the better
approach would be to only convert data back end to SQL Server, and still
use your MDB front end, with possible minor modification. After all, ADP
type of application is dying.

Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a textbox,
in a form.
In my mdb database, the syntax was like : [Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and obviously
does not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Norman Yuan]

Hi Norman,

Thank you very much for your response.
I understand how things work better now.
Because i'm used to work with mdb files, it was difficult for me to
understand that ADP files are just an interface that is not directly
connected with databases, like it is in mdb (for example, the queries).
The reason why i got interested into ADP files is that i'm trying to find
the best way (with less changes to do ans without great changes in
interfaces) to convert my mdb into SQL.
But, as you say ADP is dying, maybe this way is wrong.
I know i can convert my tables and stuff into SQL and still use mdb
interface (by changing the link of the bases), but if i want to

Yes, the is what MS recommended: use MDB front end agianst SQL Server back
end, rather then ADP.
And yes, you need to use ODBC to link tables in SQL Server database. Whether
you use ADP or MDB, you need to set up connection to SQL Server database
anyway. For ADP, the connection setup is just marginally easier than MDB.

redistribuate my application, i will have to create the dbo connection on
every computers, isn't it ?
Do you have a better solution ?

Thanks by advance.

Nesta

ADP file, not like MDB, does not contains query. The queries you see in
ADP file are SQL Server objects (Views, Stored Procedures and UDFs...).
When converting, the queries in MDB may or may not be converted to
corresponding SQL Server objects, depending on what is in the mdb
queries. In your case, your query was conerted as stored procedure, which
sits in SQL Server back end, and has no knowledge of your ADP front end
(and it should not be tied to any specific front end at all), so the
conversion smartly enough to a sptored procedure and converted the two
required inputs as input parameters. Now, when you call this stored
procedure in an front end application, such as ADP file using it as a
form's data source, you need to get user input and pass the input as the
stored procedure's parameters.

If you already have an Access application done in MDB file, the better
approach would be to only convert data back end to SQL Server, and still
use your MDB front end, with possible minor modification. After all, ADP
type of application is dying.

Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a textbox,
in a form.
In my mdb database, the syntax was like :
[Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and obviously
does not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Guest]

I am currently experimenting with upsizing a front end/back end Access 2000
database and have found that keeping the front end an mdb with the back end
converted to SQL Server 2000 results in excruciatingly SLOW execution speeds
for the queries. As I understand it, this is because the queries (about 50 of
'em) are being executed on each workstation, not the server. Some of these
queries are pretty complex, but they are essential for most, if not all, of
the reports and forms to function correctly.

So I am now testing upsizing to an adp file. Only about half of the queries
made the migration successfully, but preliminary testing of the functions
that do work indicates that the adp approach will result in much faster
response times for the user.

HOWEVER, I am concerned about the posts that are suggesting Microsoft is
dropping support for adp. One of the reasons I have been asked by the Powers
That Be to upgrade the database is to allow for future growth. I'm not sure I
can claim that adp is the future. Are there any alternatives to adp that will
allow me to put the queries on the server without having to learn a
completely new programming environment, and which will enjoy future support?
THanks!

Hi Norman,

Thank you very much for your response.
I understand how things work better now.
Because i'm used to work with mdb files, it was difficult for me to
understand that ADP files are just an interface that is not directly
connected with databases, like it is in mdb (for example, the queries).
The reason why i got interested into ADP files is that i'm trying to find
the best way (with less changes to do ans without great changes in
interfaces) to convert my mdb into SQL.
But, as you say ADP is dying, maybe this way is wrong.
I know i can convert my tables and stuff into SQL and still use mdb
interface (by changing the link of the bases), but if i want to

Yes, the is what MS recommended: use MDB front end agianst SQL Server back
end, rather then ADP.
And yes, you need to use ODBC to link tables in SQL Server database. Whether
you use ADP or MDB, you need to set up connection to SQL Server database
anyway. For ADP, the connection setup is just marginally easier than MDB.

redistribuate my application, i will have to create the dbo connection on
every computers, isn't it ?
Do you have a better solution ?

Thanks by advance.

Nesta

ADP file, not like MDB, does not contains query. The queries you see in
ADP file are SQL Server objects (Views, Stored Procedures and UDFs...).
When converting, the queries in MDB may or may not be converted to
corresponding SQL Server objects, depending on what is in the mdb
queries. In your case, your query was conerted as stored procedure, which
sits in SQL Server back end, and has no knowledge of your ADP front end
(and it should not be tied to any specific front end at all), so the
conversion smartly enough to a sptored procedure and converted the two
required inputs as input parameters. Now, when you call this stored
procedure in an front end application, such as ADP file using it as a
form's data source, you need to get user input and pass the input as the
stored procedure's parameters.

If you already have an Access application done in MDB file, the better
approach would be to only convert data back end to SQL Server, and still
use your MDB front end, with possible minor modification. After all, ADP
type of application is dying.


Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a textbox,
in a form.
In my mdb database, the syntax was like :
[Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and obviously
does not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Norman Yuan]

You must realize that "upsizing" Jet database (access dabatase) to SQL
Server is converting one database system into another different database.
Due to the difference btewwen the two, not everything is convertable without
changes. The easy part is transferring data into corresponding tables. But
queries are different stories.

Firstly, there is no exact equivalent query object in SQL Server to MDB's
query. In SQL Server, there are Views, Stored Procedures, UDFs. Most likely,
the wizard converts MDB queries to Viwes or SP, if the queries are
convertiable. (if the query contains VBA function, then it will probably not
convertible, because SQL Server does not use VBA function in View,SP,UDF).

Secondly, even some queries are converted, you still need to examine them
carefully, and may have to give them up and re-write in T-SQL to make to
queries as true, efficient SQL Server Views/SPs

Also, when you decide to use MDB front-end, you can choose use MDB queries
in the front end or use SQL Server side query objects (View/SP...). You make
this decision based on your need to the performance of your application.

All in all, to use SQL Server as back end, you need to learn more about SQL
Server. You cannot simply change data to different back end and expect your
application work as efficient as it should. SQL Server is very powerful
server software, whether you use MDB, ADP or anything else to access data
from it, you MUST learn how to use it and almost for sure you need to learn
another programming environment (T-SQL).

I am currently experimenting with upsizing a front end/back end Access 2000
database and have found that keeping the front end an mdb with the back
end
converted to SQL Server 2000 results in excruciatingly SLOW execution
speeds
for the queries. As I understand it, this is because the queries (about 50
of
'em) are being executed on each workstation, not the server. Some of these
queries are pretty complex, but they are essential for most, if not all,
of
the reports and forms to function correctly.

So I am now testing upsizing to an adp file. Only about half of the
queries
made the migration successfully, but preliminary testing of the functions
that do work indicates that the adp approach will result in much faster
response times for the user.

HOWEVER, I am concerned about the posts that are suggesting Microsoft is
dropping support for adp. One of the reasons I have been asked by the
Powers
That Be to upgrade the database is to allow for future growth. I'm not
sure I
can claim that adp is the future. Are there any alternatives to adp that
will
allow me to put the queries on the server without having to learn a
completely new programming environment, and which will enjoy future
support?
THanks!

Hi Norman,

Thank you very much for your response.
I understand how things work better now.
Because i'm used to work with mdb files, it was difficult for me to
understand that ADP files are just an interface that is not directly
connected with databases, like it is in mdb (for example, the queries).
The reason why i got interested into ADP files is that i'm trying to
find
the best way (with less changes to do ans without great changes in
interfaces) to convert my mdb into SQL.
But, as you say ADP is dying, maybe this way is wrong.
I know i can convert my tables and stuff into SQL and still use mdb
interface (by changing the link of the bases), but if i want to

Yes, the is what MS recommended: use MDB front end agianst SQL Server
back
end, rather then ADP.
And yes, you need to use ODBC to link tables in SQL Server database.
Whether
you use ADP or MDB, you need to set up connection to SQL Server database
anyway. For ADP, the connection setup is just marginally easier than MDB.

redistribuate my application, i will have to create the dbo connection
on
every computers, isn't it ?
Do you have a better solution ?

Thanks by advance.

Nesta


"Norman Yuan" <[email protected]> a écrit dans le message de %[email protected]...
ADP file, not like MDB, does not contains query. The queries you see
in
ADP file are SQL Server objects (Views, Stored Procedures and
UDFs...).
When converting, the queries in MDB may or may not be converted to
corresponding SQL Server objects, depending on what is in the mdb
queries. In your case, your query was conerted as stored procedure,
which
sits in SQL Server back end, and has no knowledge of your ADP front
end
(and it should not be tied to any specific front end at all), so the
conversion smartly enough to a sptored procedure and converted the two
required inputs as input parameters. Now, when you call this stored
procedure in an front end application, such as ADP file using it as a
form's data source, you need to get user input and pass the input as
the
stored procedure's parameters.

If you already have an Access application done in MDB file, the better
approach would be to only convert data back end to SQL Server, and
still
use your MDB front end, with possible minor modification. After all,
ADP
type of application is dying.


Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a
textbox,
in a form.
In my mdb database, the syntax was like :
[Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and
obviously
does not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Nesta]

Hi Norman,

I decided to go on with adp project, and i have created a store procedure.
This SP is called when a button is clicked on a form.
I want to set one parameter to give to the SP, but i don't know exactly how
it works with VBA.
Maybe i have to declare a variable in my form and then call th SP. In fact,
i don't know the syntax to realise that.
Could you give me an helping hand by giving me an example of a SP call with
parameter please ?
Thanks by advance.


Nesta

You must realize that "upsizing" Jet database (access dabatase) to SQL
Server is converting one database system into another different database.
Due to the difference btewwen the two, not everything is convertable
without changes. The easy part is transferring data into corresponding
tables. But queries are different stories.

Firstly, there is no exact equivalent query object in SQL Server to MDB's
query. In SQL Server, there are Views, Stored Procedures, UDFs. Most
likely, the wizard converts MDB queries to Viwes or SP, if the queries are
convertiable. (if the query contains VBA function, then it will probably
not convertible, because SQL Server does not use VBA function in
View,SP,UDF).

Secondly, even some queries are converted, you still need to examine them
carefully, and may have to give them up and re-write in T-SQL to make to
queries as true, efficient SQL Server Views/SPs

Also, when you decide to use MDB front-end, you can choose use MDB queries
in the front end or use SQL Server side query objects (View/SP...). You
make this decision based on your need to the performance of your
application.

All in all, to use SQL Server as back end, you need to learn more about
SQL Server. You cannot simply change data to different back end and expect
your application work as efficient as it should. SQL Server is very
powerful server software, whether you use MDB, ADP or anything else to
access data from it, you MUST learn how to use it and almost for sure you
need to learn another programming environment (T-SQL).

I am currently experimenting with upsizing a front end/back end Access
2000
database and have found that keeping the front end an mdb with the back
end
converted to SQL Server 2000 results in excruciatingly SLOW execution
speeds
for the queries. As I understand it, this is because the queries (about
50 of
'em) are being executed on each workstation, not the server. Some of
these
queries are pretty complex, but they are essential for most, if not all,
of
the reports and forms to function correctly.

So I am now testing upsizing to an adp file. Only about half of the
queries
made the migration successfully, but preliminary testing of the functions
that do work indicates that the adp approach will result in much faster
response times for the user.

HOWEVER, I am concerned about the posts that are suggesting Microsoft is
dropping support for adp. One of the reasons I have been asked by the
Powers
That Be to upgrade the database is to allow for future growth. I'm not
sure I
can claim that adp is the future. Are there any alternatives to adp that
will
allow me to put the queries on the server without having to learn a
completely new programming environment, and which will enjoy future
support?
THanks!

Hi Norman,

Thank you very much for your response.
I understand how things work better now.
Because i'm used to work with mdb files, it was difficult for me to
understand that ADP files are just an interface that is not directly
connected with databases, like it is in mdb (for example, the
queries).
The reason why i got interested into ADP files is that i'm trying to
find
the best way (with less changes to do ans without great changes in
interfaces) to convert my mdb into SQL.
But, as you say ADP is dying, maybe this way is wrong.
I know i can convert my tables and stuff into SQL and still use mdb
interface (by changing the link of the bases), but if i want to


Yes, the is what MS recommended: use MDB front end agianst SQL Server
back
end, rather then ADP.
And yes, you need to use ODBC to link tables in SQL Server database.
Whether
you use ADP or MDB, you need to set up connection to SQL Server database
anyway. For ADP, the connection setup is just marginally easier than
MDB.

redistribuate my application, i will have to create the dbo connection
on
every computers, isn't it ?
Do you have a better solution ?

Thanks by advance.

Nesta


"Norman Yuan" <[email protected]> a écrit dans le message de
%[email protected]...
ADP file, not like MDB, does not contains query. The queries you see
in
ADP file are SQL Server objects (Views, Stored Procedures and
UDFs...).
When converting, the queries in MDB may or may not be converted to
corresponding SQL Server objects, depending on what is in the mdb
queries. In your case, your query was conerted as stored procedure,
which
sits in SQL Server back end, and has no knowledge of your ADP front
end
(and it should not be tied to any specific front end at all), so the
conversion smartly enough to a sptored procedure and converted the
two
required inputs as input parameters. Now, when you call this stored
procedure in an front end application, such as ADP file using it as a
form's data source, you need to get user input and pass the input as
the
stored procedure's parameters.

If you already have an Access application done in MDB file, the
better
approach would be to only convert data back end to SQL Server, and
still
use your MDB front end, with possible minor modification. After all,
ADP
type of application is dying.


Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a
textbox,
in a form.
In my mdb database, the syntax was like :
[Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and
obviously
does not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Norman Yuan]

When using bound form, you can use SP as a form's RecordSource. Open a form
in design mode and open form's Propertie window, Data tab. Set ReportSource
an SP, and fill in InputParameter field. You can also click "InputParameter"
field and then press F1 to get help topic on Form's InputParameter property.

If you decide to use unbound form and use VBA code to run SP, then you need
to use ADO Command object. There are lot of examples around on ADO
programming. Here is quick psuedo code:

Private Sub cmdButton1_Click()
Dim cmd As ADODB.Command
Dim pmt As ADODB.Parameter
Set cmd=New ADODB.Command
Set cmd.ActiveConnection=CurrentProject.Connection
cmd.CommandType=adCmdStoreProc
cmd.CommandText="MySP"
Set pmt=cmd.CreateParameter("@Param1".......,myValue1)
cmd.Parameters.Append pmt
'Add more parameters if needed

'Execute sp
cmd.Execute 'No data set is returned

'Or if dataSet is returned
Dim rs AS ADODB.RecordSet
Set rs=cmd.Execute

'Then you can use the returned recordset as something's data source

End

Of course if you use unbound forms a lot, then there isn't much point to use
Access any more: you lost all the conveniences Access offers.

Hi Norman,

I decided to go on with adp project, and i have created a store procedure.
This SP is called when a button is clicked on a form.
I want to set one parameter to give to the SP, but i don't know exactly
how it works with VBA.
Maybe i have to declare a variable in my form and then call th SP. In
fact, i don't know the syntax to realise that.
Could you give me an helping hand by giving me an example of a SP call
with parameter please ?
Thanks by advance.


Nesta

You must realize that "upsizing" Jet database (access dabatase) to SQL
Server is converting one database system into another different database.
Due to the difference btewwen the two, not everything is convertable
without changes. The easy part is transferring data into corresponding
tables. But queries are different stories.

Firstly, there is no exact equivalent query object in SQL Server to MDB's
query. In SQL Server, there are Views, Stored Procedures, UDFs. Most
likely, the wizard converts MDB queries to Viwes or SP, if the queries
are convertiable. (if the query contains VBA function, then it will
probably not convertible, because SQL Server does not use VBA function in
View,SP,UDF).

Secondly, even some queries are converted, you still need to examine them
carefully, and may have to give them up and re-write in T-SQL to make to
queries as true, efficient SQL Server Views/SPs

Also, when you decide to use MDB front-end, you can choose use MDB
queries in the front end or use SQL Server side query objects
(View/SP...). You make this decision based on your need to the
performance of your application.

All in all, to use SQL Server as back end, you need to learn more about
SQL Server. You cannot simply change data to different back end and
expect your application work as efficient as it should. SQL Server is
very powerful server software, whether you use MDB, ADP or anything else
to access data from it, you MUST learn how to use it and almost for sure
you need to learn another programming environment (T-SQL).

I am currently experimenting with upsizing a front end/back end Access
2000
database and have found that keeping the front end an mdb with the back
end
converted to SQL Server 2000 results in excruciatingly SLOW execution
speeds
for the queries. As I understand it, this is because the queries (about
50 of
'em) are being executed on each workstation, not the server. Some of
these
queries are pretty complex, but they are essential for most, if not all,
of
the reports and forms to function correctly.

So I am now testing upsizing to an adp file. Only about half of the
queries
made the migration successfully, but preliminary testing of the
functions
that do work indicates that the adp approach will result in much faster
response times for the user.

HOWEVER, I am concerned about the posts that are suggesting Microsoft is
dropping support for adp. One of the reasons I have been asked by the
Powers
That Be to upgrade the database is to allow for future growth. I'm not
sure I
can claim that adp is the future. Are there any alternatives to adp that
will
allow me to put the queries on the server without having to learn a
completely new programming environment, and which will enjoy future
support?
THanks!

:


Hi Norman,

Thank you very much for your response.
I understand how things work better now.
Because i'm used to work with mdb files, it was difficult for me to
understand that ADP files are just an interface that is not directly
connected with databases, like it is in mdb (for example, the
queries).
The reason why i got interested into ADP files is that i'm trying to
find
the best way (with less changes to do ans without great changes in
interfaces) to convert my mdb into SQL.
But, as you say ADP is dying, maybe this way is wrong.
I know i can convert my tables and stuff into SQL and still use mdb
interface (by changing the link of the bases), but if i want to


Yes, the is what MS recommended: use MDB front end agianst SQL Server
back
end, rather then ADP.
And yes, you need to use ODBC to link tables in SQL Server database.
Whether
you use ADP or MDB, you need to set up connection to SQL Server
database
anyway. For ADP, the connection setup is just marginally easier than
MDB.

redistribuate my application, i will have to create the dbo
connection on
every computers, isn't it ?
Do you have a better solution ?

Thanks by advance.

Nesta


"Norman Yuan" <[email protected]> a écrit dans le message de
%[email protected]...
ADP file, not like MDB, does not contains query. The queries you see
in
ADP file are SQL Server objects (Views, Stored Procedures and
UDFs...).
When converting, the queries in MDB may or may not be converted to
corresponding SQL Server objects, depending on what is in the mdb
queries. In your case, your query was conerted as stored procedure,
which
sits in SQL Server back end, and has no knowledge of your ADP front
end
(and it should not be tied to any specific front end at all), so the
conversion smartly enough to a sptored procedure and converted the
two
required inputs as input parameters. Now, when you call this stored
procedure in an front end application, such as ADP file using it as
a
form's data source, you need to get user input and pass the input as
the
stored procedure's parameters.

If you already have an Access application done in MDB file, the
better
approach would be to only convert data back end to SQL Server, and
still
use your MDB front end, with possible minor modification. After all,
ADP
type of application is dying.


Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a
textbox,
in a form.
In my mdb database, the syntax was like :
[Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and
obviously
does not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Nesta]

Hi Norman,

thank you for all the explanations.
I adapted and tried the part of code you gave me to execute a SP in VBA. And
it almost worked.
But i still have some troubles....
I have a module in the one is contained a sub that receive parameters (in
that case, NumeroMag as an integer)
Here is the code i made :

Dim ProcedureStockee As ADODB.Command
Dim Parametre1 As ADODB.Parameter
Dim Results As Recordset


Set ProcedureStockee = New ADODB.Command
Set ProcedureStockee.ActiveConnection = CurrentProject.Connection
ProcedureStockee.CommandType = adCmdStoreProc

ProcedureStockee.CommandText = "SP_ResultatRecherche1"
Set Parametre1 = ProcedureStockee.CreateParameter("@LeNumIncident",
adInteger, adParamInput, 8, NumeroIncident)
ProcedureStockee.Parameters.Append Parametre1

Set Results = ProcedureStockee.Parameters.Execute


I have the following message : Member of method of data not found
(traduction from french !). Do you know why ?
1) What is adParamInput ?
2) What is the parameter Siez that i have to give in my "CreatParameter" ?
3) the parameter "adCmdStoreProc" for the CommandType is not recognizen. Do
you know why ?

I tried to get some more information for the Help, but nothing is given to
me (blank pages)...
Thanks bay advance for your help !



Nesta

When using bound form, you can use SP as a form's RecordSource. Open a
form in design mode and open form's Propertie window, Data tab. Set
ReportSource an SP, and fill in InputParameter field. You can also click
"InputParameter" field and then press F1 to get help topic on Form's
InputParameter property.

If you decide to use unbound form and use VBA code to run SP, then you
need to use ADO Command object. There are lot of examples around on ADO
programming. Here is quick psuedo code:

Private Sub cmdButton1_Click()
Dim cmd As ADODB.Command
Dim pmt As ADODB.Parameter
Set cmd=New ADODB.Command
Set cmd.ActiveConnection=CurrentProject.Connection
cmd.CommandType=adCmdStoreProc
cmd.CommandText="MySP"
Set pmt=cmd.CreateParameter("@Param1".......,myValue1)
cmd.Parameters.Append pmt
'Add more parameters if needed

'Execute sp
cmd.Execute 'No data set is returned

'Or if dataSet is returned
Dim rs AS ADODB.RecordSet
Set rs=cmd.Execute

'Then you can use the returned recordset as something's data source

End

Of course if you use unbound forms a lot, then there isn't much point to
use Access any more: you lost all the conveniences Access offers.

Hi Norman,

I decided to go on with adp project, and i have created a store
procedure.
This SP is called when a button is clicked on a form.
I want to set one parameter to give to the SP, but i don't know exactly
how it works with VBA.
Maybe i have to declare a variable in my form and then call th SP. In
fact, i don't know the syntax to realise that.
Could you give me an helping hand by giving me an example of a SP call
with parameter please ?
Thanks by advance.


Nesta

You must realize that "upsizing" Jet database (access dabatase) to SQL
Server is converting one database system into another different
database. Due to the difference btewwen the two, not everything is
convertable without changes. The easy part is transferring data into
corresponding tables. But queries are different stories.

Firstly, there is no exact equivalent query object in SQL Server to
MDB's query. In SQL Server, there are Views, Stored Procedures, UDFs.
Most likely, the wizard converts MDB queries to Viwes or SP, if the
queries are convertiable. (if the query contains VBA function, then it
will probably not convertible, because SQL Server does not use VBA
function in View,SP,UDF).

Secondly, even some queries are converted, you still need to examine
them carefully, and may have to give them up and re-write in T-SQL to
make to queries as true, efficient SQL Server Views/SPs

Also, when you decide to use MDB front-end, you can choose use MDB
queries in the front end or use SQL Server side query objects
(View/SP...). You make this decision based on your need to the
performance of your application.

All in all, to use SQL Server as back end, you need to learn more about
SQL Server. You cannot simply change data to different back end and
expect your application work as efficient as it should. SQL Server is
very powerful server software, whether you use MDB, ADP or anything else
to access data from it, you MUST learn how to use it and almost for sure
you need to learn another programming environment (T-SQL).

I am currently experimenting with upsizing a front end/back end Access
2000
database and have found that keeping the front end an mdb with the back
end
converted to SQL Server 2000 results in excruciatingly SLOW execution
speeds
for the queries. As I understand it, this is because the queries (about
50 of
'em) are being executed on each workstation, not the server. Some of
these
queries are pretty complex, but they are essential for most, if not
all, of
the reports and forms to function correctly.

So I am now testing upsizing to an adp file. Only about half of the
queries
made the migration successfully, but preliminary testing of the
functions
that do work indicates that the adp approach will result in much faster
response times for the user.

HOWEVER, I am concerned about the posts that are suggesting Microsoft
is
dropping support for adp. One of the reasons I have been asked by the
Powers
That Be to upgrade the database is to allow for future growth. I'm not
sure I
can claim that adp is the future. Are there any alternatives to adp
that will
allow me to put the queries on the server without having to learn a
completely new programming environment, and which will enjoy future
support?
THanks!

:


Hi Norman,

Thank you very much for your response.
I understand how things work better now.
Because i'm used to work with mdb files, it was difficult for me to
understand that ADP files are just an interface that is not directly
connected with databases, like it is in mdb (for example, the
queries).
The reason why i got interested into ADP files is that i'm trying to
find
the best way (with less changes to do ans without great changes in
interfaces) to convert my mdb into SQL.
But, as you say ADP is dying, maybe this way is wrong.
I know i can convert my tables and stuff into SQL and still use mdb
interface (by changing the link of the bases), but if i want to


Yes, the is what MS recommended: use MDB front end agianst SQL Server
back
end, rather then ADP.
And yes, you need to use ODBC to link tables in SQL Server database.
Whether
you use ADP or MDB, you need to set up connection to SQL Server
database
anyway. For ADP, the connection setup is just marginally easier than
MDB.

redistribuate my application, i will have to create the dbo
connection on
every computers, isn't it ?
Do you have a better solution ?

Thanks by advance.

Nesta


"Norman Yuan" <[email protected]> a écrit dans le message de
%[email protected]...
ADP file, not like MDB, does not contains query. The queries you
see in
ADP file are SQL Server objects (Views, Stored Procedures and
UDFs...).
When converting, the queries in MDB may or may not be converted to
corresponding SQL Server objects, depending on what is in the mdb
queries. In your case, your query was conerted as stored procedure,
which
sits in SQL Server back end, and has no knowledge of your ADP front
end
(and it should not be tied to any specific front end at all), so
the
conversion smartly enough to a sptored procedure and converted the
two
required inputs as input parameters. Now, when you call this stored
procedure in an front end application, such as ADP file using it as
a
form's data source, you need to get user input and pass the input
as the
stored procedure's parameters.

If you already have an Access application done in MDB file, the
better
approach would be to only convert data back end to SQL Server, and
still
use your MDB front end, with possible minor modification. After
all, ADP
type of application is dying.


Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a
textbox,
in a form.
In my mdb database, the syntax was like :
[Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and
obviously
does not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Norman Yuan]

Hi Norman,

thank you for all the explanations.
I adapted and tried the part of code you gave me to execute a SP in VBA.
And it almost worked.
But i still have some troubles....
I have a module in the one is contained a sub that receive parameters (in
that case, NumeroMag as an integer)
Here is the code i made :

Dim ProcedureStockee As ADODB.Command
Dim Parametre1 As ADODB.Parameter
Dim Results As Recordset


Set ProcedureStockee = New ADODB.Command
Set ProcedureStockee.ActiveConnection = CurrentProject.Connection
ProcedureStockee.CommandType = adCmdStoreProc

ProcedureStockee.CommandText = "SP_ResultatRecherche1"
Set Parametre1 = ProcedureStockee.CreateParameter("@LeNumIncident",
adInteger, adParamInput, 8, NumeroIncident)
ProcedureStockee.Parameters.Append Parametre1

Set Results = ProcedureStockee.Parameters.Execute


I have the following message : Member of method of data not found
(traduction from french !). Do you know why ?

Do not know why. Which line of code caused this?

1) What is adParamInput ?

Indicates this parameter is for input. If you need retrieve a value from
SP's output parameter, you need to use adParameterOutput

2) What is the parameter Siez that i have to give in my "CreatParameter" ?

You do not need to specify size for Interger,double, boolean..., so you code
would look like

Set Parametre1 = ProcedureStockee.CreateParameter("@LeNumIncident",
adInteger, adParamInput, , NumeroIncident)

However, if the parameter used for the SP is text type, such as
char,nchar,varchar,nvarchar...yu need to give the size (the length of the
text field).

3) the parameter "adCmdStoreProc" for the CommandType is not recognizen.
Do you know why ?

it was my typo. It should be "acCmdStoredProc". You should get intellisense
prompt when you typing code in VBA.

I tried to get some more information for the Help, but nothing is given to
me (blank pages)...
Thanks bay advance for your help !

Just set a break point at certain line of code and debug the code line by
line.

Nesta

When using bound form, you can use SP as a form's RecordSource. Open a
form in design mode and open form's Propertie window, Data tab. Set
ReportSource an SP, and fill in InputParameter field. You can also click
"InputParameter" field and then press F1 to get help topic on Form's
InputParameter property.

If you decide to use unbound form and use VBA code to run SP, then you
need to use ADO Command object. There are lot of examples around on ADO
programming. Here is quick psuedo code:

Private Sub cmdButton1_Click()
Dim cmd As ADODB.Command
Dim pmt As ADODB.Parameter
Set cmd=New ADODB.Command
Set cmd.ActiveConnection=CurrentProject.Connection
cmd.CommandType=adCmdStoreProc
cmd.CommandText="MySP"
Set pmt=cmd.CreateParameter("@Param1".......,myValue1)
cmd.Parameters.Append pmt
'Add more parameters if needed

'Execute sp
cmd.Execute 'No data set is returned

'Or if dataSet is returned
Dim rs AS ADODB.RecordSet
Set rs=cmd.Execute

'Then you can use the returned recordset as something's data source

End

Of course if you use unbound forms a lot, then there isn't much point to
use Access any more: you lost all the conveniences Access offers.

Hi Norman,

I decided to go on with adp project, and i have created a store
procedure.
This SP is called when a button is clicked on a form.
I want to set one parameter to give to the SP, but i don't know exactly
how it works with VBA.
Maybe i have to declare a variable in my form and then call th SP. In
fact, i don't know the syntax to realise that.
Could you give me an helping hand by giving me an example of a SP call
with parameter please ?
Thanks by advance.


Nesta


"Norman Yuan" <[email protected]> a écrit dans le message de [email protected]...
You must realize that "upsizing" Jet database (access dabatase) to SQL
Server is converting one database system into another different
database. Due to the difference btewwen the two, not everything is
convertable without changes. The easy part is transferring data into
corresponding tables. But queries are different stories.

Firstly, there is no exact equivalent query object in SQL Server to
MDB's query. In SQL Server, there are Views, Stored Procedures, UDFs.
Most likely, the wizard converts MDB queries to Viwes or SP, if the
queries are convertiable. (if the query contains VBA function, then it
will probably not convertible, because SQL Server does not use VBA
function in View,SP,UDF).

Secondly, even some queries are converted, you still need to examine
them carefully, and may have to give them up and re-write in T-SQL to
make to queries as true, efficient SQL Server Views/SPs

Also, when you decide to use MDB front-end, you can choose use MDB
queries in the front end or use SQL Server side query objects
(View/SP...). You make this decision based on your need to the
performance of your application.

All in all, to use SQL Server as back end, you need to learn more about
SQL Server. You cannot simply change data to different back end and
expect your application work as efficient as it should. SQL Server is
very powerful server software, whether you use MDB, ADP or anything
else to access data from it, you MUST learn how to use it and almost
for sure you need to learn another programming environment (T-SQL).

I am currently experimenting with upsizing a front end/back end Access
2000
database and have found that keeping the front end an mdb with the
back end
converted to SQL Server 2000 results in excruciatingly SLOW execution
speeds
for the queries. As I understand it, this is because the queries
(about 50 of
'em) are being executed on each workstation, not the server. Some of
these
queries are pretty complex, but they are essential for most, if not
all, of
the reports and forms to function correctly.

So I am now testing upsizing to an adp file. Only about half of the
queries
made the migration successfully, but preliminary testing of the
functions
that do work indicates that the adp approach will result in much
faster
response times for the user.

HOWEVER, I am concerned about the posts that are suggesting Microsoft
is
dropping support for adp. One of the reasons I have been asked by the
Powers
That Be to upgrade the database is to allow for future growth. I'm not
sure I
can claim that adp is the future. Are there any alternatives to adp
that will
allow me to put the queries on the server without having to learn a
completely new programming environment, and which will enjoy future
support?
THanks!

:


Hi Norman,

Thank you very much for your response.
I understand how things work better now.
Because i'm used to work with mdb files, it was difficult for me to
understand that ADP files are just an interface that is not
directly
connected with databases, like it is in mdb (for example, the
queries).
The reason why i got interested into ADP files is that i'm trying
to find
the best way (with less changes to do ans without great changes in
interfaces) to convert my mdb into SQL.
But, as you say ADP is dying, maybe this way is wrong.
I know i can convert my tables and stuff into SQL and still use mdb
interface (by changing the link of the bases), but if i want to


Yes, the is what MS recommended: use MDB front end agianst SQL Server
back
end, rather then ADP.
And yes, you need to use ODBC to link tables in SQL Server database.
Whether
you use ADP or MDB, you need to set up connection to SQL Server
database
anyway. For ADP, the connection setup is just marginally easier than
MDB.

redistribuate my application, i will have to create the dbo
connection on
every computers, isn't it ?
Do you have a better solution ?

Thanks by advance.

Nesta


"Norman Yuan" <[email protected]> a écrit dans le message de
%[email protected]...
ADP file, not like MDB, does not contains query. The queries you
see in
ADP file are SQL Server objects (Views, Stored Procedures and
UDFs...).
When converting, the queries in MDB may or may not be converted to
corresponding SQL Server objects, depending on what is in the mdb
queries. In your case, your query was conerted as stored
procedure, which
sits in SQL Server back end, and has no knowledge of your ADP
front end
(and it should not be tied to any specific front end at all), so
the
conversion smartly enough to a sptored procedure and converted the
two
required inputs as input parameters. Now, when you call this
stored
procedure in an front end application, such as ADP file using it
as a
form's data source, you need to get user input and pass the input
as the
stored procedure's parameters.

If you already have an Access application done in MDB file, the
better
approach would be to only convert data back end to SQL Server, and
still
use your MDB front end, with possible minor modification. After
all, ADP
type of application is dying.


Hi all,

I'm testing migration from Acess database to ADP Project.
I have this query wich criteria of selection is the value of a
textbox,
in a form.
In my mdb database, the syntax was like :
[Forms]![F_MyForm]![MyTextBox].

In my ADP project, i have noticed theses changes :
- my query was transformed into a function ;
- the syntax ro reach the value of my TexBox has changed (and
obviously
does not work) : = @Forms_F_MyForm_MyTextBox.

1) How to find the good syntax ?
2) Where can I find ressources on ADP syntax ?


Thanks by advance !

Nesta

 

[Charles ERNST]

An easier way to pass parameters :

Dim C As New ADODB.Command
On Error GoTo execError
Set C.ActiveConnection = CurrentProject.Connection
C.CommandType = adCmdStoredProc
C.CommandText = "[Maj Prix Clients]"
C.Parameters.Refresh
C.Parameters("@Client") = Me("Client")
C.Parameters("@DOrigine") = Me("Devise").OldValue
C.Parameters("@EuroOrigine") = DLookup("[Euro]", "Devises",
"[Devise]='" & Me("Devise").OldValue & "'")
C.Parameters("@TxO") = DLookup("[Taux]", "Devises", "[Devise]='" &
Me("Devise").OldValue & "'")
C.Parameters("@DFinale") = Me("Devise")
C.Parameters("@EuroFinal") = DLookup("[Euro]", "Devises",
"[Devise]='" & Me("Devise") & "'")
C.Parameters("@TxF") = DLookup("[Taux]", "Devises", "[Devise]='" &
Me("Devise") & "'")
C.Execute
Set C = Nothing
exit sub

execError:
If C.ActiveConnection.Errors.Count > 0 Then
For Each errLoop In C.ActiveConnection.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume Next

 

[Nesta]

Hi and thank you for this part of code,

I tried it in a module and it works. At least, the store procedure is
executed without any errors.
My question now is how to get the results back, or even to display them.
Here is an example :

I made a form about students.
There is a combo box. In the event prodecure "Combo_AfterUpdate()", the
fields showing infos about the kids must be refreshed with the correspondant
infos.
Here is the code of Combo_AfterUpdate() :
-------------------------------------
SP.AppelSP (Me.Combo.Value)
-------------------------------------

- SP is the name of my module.
- AppelSP is the name of the sub containing the code for the store
procedure.
- Me.Combo.Value is the value for the parameter to be taken.

Here is the code in SP :
-----------------------------------------------------------
Sub AppelSP(Param1)
On Error GoTo execError


Dim LaCommande As New ADODB.Command

' Récupération des informations :
With LaCommande
Set .ActiveConnection = CurrentProject.Connection
'mTechnique.U.bddConnection
.CommandType = adCmdStoredProc
.CommandText = "spEleve"
.Parameters.Refresh
.Parameters("@prenom") = Param1
.Execute
End With

Set LaCommande = Nothing
-----------------------------------------------------------

All this code is executed correctly. I just don't know how to diplay the
results in the fields of my form.
I heard about recordset that can be set to get the results back. But for the
rest, i have no mor info.
Coul you help me please ?
Do you have any example of code, or even ADP project that could give me an
helping hand ?
Thanks a lot by advance.


Nesta