Pass Parameter to SQL Server Stored Procedure

[Fubear]

I am upsizing my database to SQL server, and have cone across a small problem.

I have two comboboxes on one form. The first holds a title, and the second holds the value for that title.

The record source is a table which brings them together using three fields: ParentID,TitleID,RatingID

The Title Combobox works fine with an SQL select statement. The rating combobox on the other hand doesnt.

Its old code was:
SELECT [optCompRatings].[Value] FROM optCompRatings WHERE [optCompRatings].[CompID]=TitleCombo.value

now the upsizing wizard has replaced this with a parameter procedure that returns the correct results. Problem is that it now prompts me to enter the value.

The rowsource of the combo is: ut_qry5

i have tried: ut_qry5 (TitleCombo.Value), ut_qry5 @TitleCombo_Value=TitleCombo.Value and other small variations.

I cant find anything in the help files and it is driving me mad. The function needs to be run every time the TitleCombo changes its value.

I'm really pulling my hair out on this one - I seriously hope someone can help me out.

 

[Fubear]

I have done some research on this, and i THINK i need to use ADO.

I havnt been using access for too long, and I havnt douched ADO, DAO, or any such code.

I desperately need to be able to use it though, so if anyone can let me know where I can get ahold of some good (free)learning materials online, or give some example code it would be fantastic.

The help files for DAO and ADO are missing here, and i cant find much learning material, so im coding critical application blind.

If i could give 100 Stars to someone who can give me something to chew on over the weekend so i can pretend to be a ADO Guru when i get into work on monday, I would.

 

[cmmrfrds]

I use this microsoft link to get code examples on DAO and ADO. It shows many examples of DAO and ADO code that accomplish the same purpose using the different libraries.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

 

[cmmrfrds]

When you upsized are you using an Access Project (adp) since the defaults are different between an adp and mdb.

The general syntax for passing parms is to include as a comma delimited list. If it is prompting then the parm is not included for some reason. ADO will check the parameter type so a text parm when it should be numeric will cause and error.

ut_qry5 parm1, parm2

 

[Fubear]

Yes im using an access project file now

Some of the attempts i tried got around the dialogue asking for the parameter, but i got an error, something about unknown or unrecognised property.

Do i have to use DAO or ADO to call the function? Or can i just enter "ut_qry5 TitleCombo.Value" as the rowsource for my 2nd combo, and just requery it when the first combo changes.

I really like the added flexibility and efficiency i get from upsizing to SQL server, but im feeling very crippled by VB and how there are so many obscure topics that I find would have made my life 100x easier had i found out about them on day 1 of using access.

Today was my first day using Access Projects, and I have 2 weeks to upsize a large database i spent the last 3 months working on. This is the only bit i really dont get, so im hoping it will be a realtively easy ride onve im over this hurdle.

 

[cmmrfrds]

What you have said should work provided there is only 1 parameter and the data type is correct.

One thing to be aware of is all databases in sql server are owned. I always create mind with dbo as the owner since it makes things easier overall. It is a good idea to always include the owner in your sql statements. The "sa" login has access to all objects other logins are given permissions. So, if you test under sa and the user tests under another login they may get an error without the dbo prefix.

select * from dbo.yourtable
and
exec dbo.sp_myproc
exec dbo.ut_qry5 parm1

Another thing to be aware of, all data sources default to ADO data objects in an Access Project, while in an Access MDB all data sources default to DAO. There are a couple of syntax differences between ADO and DAO. In ADO % is the wildcard while in DAO * is the wildcard, and ADO uses single quotes instead of double quotes for literals.

 

[Fubear]

Now your just confusing me. I've never used SQL server, I was just given the server address and login/pass for a limited access account by the DBA.

I pick things up really easy, but im finding lack of documentation to be an annoyance. This site has been great for me.

I have picked up a copy of the Access 2000 Developers Handboox Vol1&2 and the Access Cookbook. Hopefully those should really help me through my current projects.