Re: From .mdb to .adp database

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.


"Nesta" <nestaman@xxxxxxxxxxx> wrote in message news:esMlLwOFIHA.536@xxxxxxxxxxxxxxxxxxxxxxx
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" <NoAddress@xxxxxxxxxxxx> a écrit dans le message de news: uRNGC7$EIHA.5228@xxxxxxxxxxxxxxxxxxxxxxx
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).

"el zorro" <elzorro@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:EDA7D600-99F2-48DD-A822-0228116C17AF@xxxxxxxxxxxxxxxx
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!

"Norman Yuan" wrote:


"Nesta" <nestaman@xxxxxxxxxxx> wrote in message
news:u9lMJpnEIHA.748@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <NoAddress@xxxxxxxxxxxx> a écrit dans le message de > news:
> %23Xr2UEcEIHA.3332@xxxxxxxxxxxxxxxxxxxxxxx
>> 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" <nestaman@xxxxxxxxxxx> wrote in message
>> news:ul6lgVaEIHA.3916@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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
>>>
>>>
>>
>
>






.