How can I convert a query in Acess 2000 .mdb to Access 2000 .adp

[zachsiue]

I have created a database using Access 2000. I created the database in the application or .mdb form. I used several queries to create the pull downs in my forms. Now I'm converting my database to the .adp or project format and it is not allowing me to import, export, or copy a query to the .adp. I can import the forms, but they are useless without the queries. Is there a way that I can create these queries in the .adp file. I am a novice with databases, so if you could provide as much detail as possible it would be greatly appreciated.
Thank You in Advance!!!!!

Zach

 

[Terpsfan]

Usually if a query does not export to an ADP it's because there are Access SQL specific functions that aren't used in SQL Server ANSI SQL. You'll have to see if you are using any functions in that specific query and convert the function to a SQL Server SQL function. Then you want to determine whether this Access query is an action query or select query. If it's an action query you want to export it as a SQL Server stored procedure, otherwise it would be a SQL Server view.

What I normally do first is use the upsizing wizard within Access to export as much as I can to the ADP, then print out the upsizing report. This report will show you what upsized and what didn't and if it didn't, why it didn't. This will greatly help you diagnose your upsizing problems.

If you would like to post the SQL statement in ACCESS feel free.

 

[zachsiue]

I ran the upsizing wizard and it said that it successfully created the queries. Everything is very close to working. Thank you a ton!!
The only problem that I am having is with in my form. In the form I have a combo box that after update runs a query that gives the selections for the next combo box. After I select an option from the first cbo, I clicked on the second cbo and recieved the following error: Must Declare the variable 'Forms_Test_TestDetail_ActionTy.' . It is the variable that determines what the query looks for, where should I declare this at? I have never worked in SQL before, so as much detail as possible is appreciated. Once again, thank you for your help so far, I'm almost there!!!!

THANK YOU!!!
Zach

 

[Terpsfan]

I'm glad to see your queries upsized. Usually you make variable declarations in the declarations section of the module, after Option Explicit/Option Compare Database. What type of variable is this, (string or object) and what is the variable pointing to?

Option Explict
Option Compare Database
Dim variablename as variabletype

 

[zachsiue]

This may clear up what I in the last reply in case it was too confusing.

The query is looking for the parameter to search with. In access the query used this line

cboaction.Action = [Forms]![Test]![TestDetail]![ActionType]

which is the result of the first cbo in my form. When it was converted to SQL this path was converted to

cboaction.Action = @Forms_Test_TestDetail_ActionType

This is the variable that the SQL project wants to be declared. Do I just need to declare it or do I need to write the path name in a different form in SQL? Hope this clears things up!!

Thanks Again!!

 

[cmmrfrds]

It will be interesting if anybody can supply an answer for inputing stored procedure parameters for a combobox. I tried working with the combobox properties and did not find a way to do it, but there are work arounds. Here are a couple of threads.

thread701-457149
thread181-477363

 

[Terpsfan]

Zach, it's looking for a parameter in your stored procedure that is pointing to your combo box on the form. SQL Server stored procedures don't use form references in there syntax, so that's why it gave you @etc. Can you copy and paste that stored procedure so I can take a look at it?

 

[Terpsfan]

The way this is going to work is that you call the stored procedure in your form code with an ADO command object. The stored procedure should have an output parameter (what will display in the second combo) as well as an input parameter (the selection from the first combo). I will try to provide an example asap.