getting error while trying to send input parameter to a store proc in ADP

Hi Experts,
I have the following code, trying to send input parameters to a store procedure that's being called thru a form in Access, however I am getting an prompt message re the second parameter when I am actually sending a value, please let me know what am I doing wrong here.
    For i = 1 To 3
        sInputParam = "@intCategory int = " & i & ", " _
            & "@strFilter nvarchar(4000) = " & strFinal
        Me.TabCtl0.Pages(1).Controls("EmpStatisticsSubFrm" & i).Form.InputParameters = sInputParam
        Me.TabCtl0.Pages(1).Controls("EmpStatisticsSubFrm" & i).Form.RecordSource = "procStatistics"
        Me.TabCtl0.Pages(1).Controls("EmpStatisticsSubFrm" & i).Form.Requery
        
    Next
    

Open in new window

LVL 5
bfuchsAsked:
Who is Participating?
 
BitsqueezerCommented:
Hi Ben,

ah OK, I think the problem here is that you have a string value IN the string and a filter string...so you need to double the single quotes IN the string like this:

strFinal = Replace(strFinal, "'", "''")

Open in new window


So in the end your InputParameter would print out:

@intCategory int = 1, @strFilter nvarchar(4000) = 'State = ''NJ'''

Open in new window


Cheers,

Christian
0
 
Duy PhamFreelance IT ConsultantCommented:
Second parameter is a text, so it should be wrapped in single quotes, e.g.:
    For i = 1 To 3
        sInputParam = "@intCategory int = " & i & ", " _
            & "@strFilter nvarchar(4000) = '" & strFinal & "'"
        Me.TabCtl0.Pages(1).Controls("EmpStatisticsSubFrm" & i).Form.InputParameters = sInputParam
        Me.TabCtl0.Pages(1).Controls("EmpStatisticsSubFrm" & i).Form.RecordSource = "procStatistics"
        Me.TabCtl0.Pages(1).Controls("EmpStatisticsSubFrm" & i).Form.Requery        
    Next    

Open in new window

0
 
BitsqueezerCommented:
Hi Ben,

I agree with Duy Pham, that should solve your problem.
Only want to add some other points: Controls in a tab control don't need to be accessed through the whole path. As the subform container controls are part of the main form you can directly access them, and also a "With" makes the code more readable, so you get

For i = 1 To 3
        sInputParam = "@intCategory int = " & i & ", " _
            & "@strFilter nvarchar(4000) = '" & strFinal & "'"
		With Me.Controls("EmpStatisticsSubFrm" & i).Form
			.InputParameters = sInputParam
			.RecordSource = "dbo.procStatistics"
			.Requery
		End With
Next

Open in new window


Cheers,

Christian
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bfuchsAuthor Commented:
Hi Experts,

I will test it next week when get back to the office.
Thanks for replying & have a nice weekend.

Ben
0
 
bfuchsAuthor Commented:
Hi,
See attached what I get when include the single quotes.
Untitled.png
0
 
BitsqueezerCommented:
Hi Ben,

the filter string looks OK, if the outer single quotes are not part of the filter and if the field names are included in the SP SELECT field list.

You should create a breakpoint and print the contents of "sInputParam" of your loop to see what's the result string.

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Hi Bit,

Its also interesting for me as with one parameter it works fine,
Attached you will see the breakpoint result string, and the outcome.
And just to make sure, the filter string is valid, when I run it direct from the DB container, It works.

Thanks,
Ben
Untitled.png
Untitled1.png
0
 
BitsqueezerCommented:
Hi Ben,

unfortunately I don't see the string, the screenshot shows some other code using ServerFilter. You should avoid taking screenshots, simply copy/paste the text and use the "Code" tags of the editor here, makes it really easier. By the way: If you need to take screenshots, try ALT-PrtScr instead of PrtScr, this copies only the active window into the clipboard.

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Hi Bit,

below is what I got from debug window for the sInputParam
@intCategory int = 1, @strFilter nvarchar(4000) = 'State = 'NJ''

Open in new window


I know when sending parameters to stored procedures in this manner, it usually does not require single quotes when the code is referring for a controls value, like the following that works fine by me.
strWhere text = Forms!FilterOrientationNotesFrm.TextstrWhere

Open in new window


Also tried without the '@' as that code, but didn't help.

I have tested with Access 2000 & 2003, both are returning more or less the same error messages.


ps. Actually the reason I captured the screenshot by print screen was that I wanted to get both, the code & the debug window at the same time, but looks like it got cut off..sorry for that.

Thanks,
Ben
0
 
BitsqueezerCommented:
Hi Ben,

if that is not a typo, you have one extra ' at the end of the line..

The single quotes are needed if you use a constant value, the Form reference is no string.
The "@" is of course also needed as that's what the SP expects as variable name.

A little bit confusing in the syntax is that you can add the datatype (like "int") to the InputParameter property, you can leave that out if you want, I personally prefer to make sure the datatype is correct when it will be sent.

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Why?

The filter string is composed of the inner single quotes as state = 'NJ' and then if you look at your code above, there is another single quote at the end after the strFinal.

Do you see more then two at the end?
0
 
BitsqueezerCommented:
If that was really the output in your immediate window (with the extra single quote) then I would say it comes from strFinal as it cannot come from the code above where there is only one single quote. So you must go step by step through your code and see what's in the variables in the Locals window.
0
 
bfuchsAuthor Commented:
Hi Bit,

There must be something I am missing here, as I can clearly understand the reason of two single quotes appearing there, since the strFinal contains one (state = 'NJ') and the code adds another one, 1+1..

However I can check the locals window to see if that's giving any clue to the issue here.
0
 
bfuchsAuthor Commented:
Hi Bit,

You got it, in 2003 its already working!!

However in A2000, I am still getting a prompt see attached.

FYI- in 2000 the prompt contains only the words before the equal sign (State), as opposed to A2003 that the prompt was of the full string (State = 'NJ').

ps. let me know if that's a complicated issue, I will post another question.

Thanks,
Ben
untitled.bmp
0
 
BitsqueezerCommented:
Hi Ben,

unfortunately I do not have A2000 and can't remember the settings there, really long ago...:-)
Why do you go on using it as you can use A2007 runtime which is free for the users?

I can only guess, maybe A2000 had not the possibility to insert the datatypes in the string, try to use it without "int" and "nvarchar(4000)". You should also look into the A2000 help for the property "InputParameters", maybe you find some hints there.

In general you can say, if you see that prompt there's something wrong in the string. You can also try to use SQL Server Profiler to see what Access has sent to the server in the background if you do not find that out from the variables in the frontend.

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Hi Bit,

Indeed it is working in both versions, looks like I mistakenly tested without your last suggestion of adding another single quote.
Thanks again for your wonderful help!

With Appreciation,
Ben
0
 
bfuchsAuthor Commented:
Why do you go on using it as you can use A2007
In short I think you have answered this question is the following post.
http://www.experts-exchange.com/Database/MS_Access/Q_28663728.html#a40752008

The real answer for this is, that we saw tremendous downgrade in performance when upgrading to 2003, and since then our manager its not approving further upgrades (unless of course I can prove it will also be an upgrade of performance).

Basically he is saying exactly the words you bought up there in the discussion with Pat,
There are people out there which also develop A97 databases - and why not if that fits their needs? We are really not dependent on MS updates and recommendations..

To top it off, the decision of Microsoft to deprecate ADP is what really turned me off, as this means I will have to abandon our ADP project if I am to follow the full path Microsoft suggests..

Just as aside, I had designed small Access MDB app in the past using 2000/3, and almost every user who had upgraded to 2007 (without my consent) was complaining of slow performance..
0
 
BitsqueezerCommented:
Hi Ben,

Basically he is saying exactly the words you bought up there in the discussion with Pat,
"There are people out there which also develop A97 databases - and why not if that fits their needs? We are really not dependent on MS updates and recommendations.."

Maybe you misunderstood the point here..:-)
It was not my intention to say "Throw away all modern versions of Access, go back to 97". The point here was that I wanted to say that it is possible to do that and additionally, if you have running versions you can stay at this version and it will not stop running in future.
In fact, I would not use a version prior to A2007 (which in my opinion is the really best and stablest version where it was A2003 before). The simplest reason is the modern style, I love the navigation bar against the database window of prior versions where you always need to switch between windows, I strongly prefer the tabbed forms against the untidy confusing many-windows interface of all prior versions where it is hard to bring the desired form to the front, I also love the ribbon although it's sometimes a little bit slow in reaction, it looks very much better and I consequently ban any possible button from my forms and offer buttons in the ribbons instead. Additionally I get a complete form in good looking design with a simple click where all prior versions looks like forms made in 1990 - you would not believe how much change it makes for the user if you offer a good-looking form, you will be surprised that often users will also say that it's faster now although nothing have changed but the design. The form is what the user sees, anything behind maybe as much professional as you want but if you have a trash-looking form the user will say that this is an old-fashioned slow program, simply because in his mind all programs which looks that way must be old and old programs must be slow. Try it out...;-)
But the advantages are more than that regarding design: Since A2007 all elements can be grouped in so-called "layouts" which removed the most annoying thing in former versions: You don't need to twiddle controls to be aligned in size and position, which is really annoying if you have a continous form with x fields beneath each other and you must change the order of fields, costs a lot of time. In A2007 with layouts you simply drag and drop the field in the layout and everything moves automatically, including the label in the header. That also works in ADPs although the file format is 2003. In A2010 you have additionally designs where you tell the color properties that they should not use color X but a color index from a design so you can change the design (fonts, colors) of all elements with one simple click.
Since A2007 you also have anchoring which means that controls can be set up to "anchor" to each corner of the form, i.e. an "OK" button in a dialog can be fixed to the lower right corner of the dialog so that it is always placed here undependent of what the user does with the form size. Controls can be stretched automatically so that resizing of the form also moves and/or resizes the controls, without any programming.

So, it's not all bad in new versions and I'm quite sure that the performance is not slower than in versions before. You can slow down everything with wrong programming or wrong usage of properties. I.e., if you create a view with a TOP clause and ORDER BY clause and use that as RecordSource of a form you will get a bad performance although it is executed very fast - just because Access waits for all the data before displaying it as SQL Server starts with sending the data when it is loaded and sorted completely. In opposite, if you remove these clauses and let Access do the ordering, the form will display in microseconds although the execution is slower - just because Access loads the data needed for the first page of the form really fast (because SQL Server starts sending the data immediately) and then load all the rest slowly in the background. But for the user it seems to be faster than the first version. Not to mention correct indexing which is the main power switch in case of performance.

Finally, to mention business decisions: Until A2010 you can go on using ADPs and so there is nothing which speaks against using the newest possible version. The investment is a lot better than with versions prior to A2007: A2003 and lower needs to purchase a development version which contains the runtime. Since A2007 the runtime can be downloaded by everyone without any licence cost so the final investment is only one full version for the developer (or one for each developer) and all the users can upgrade without any cost. The forms can be developed and changed very much faster than before (and if layouts used they never look that untidy as your search form here..;-) ).

The mentioned new options are a so very deep cut between <=A2003 and >=A2007 that I personally would not use any version prior to A2007 anymore as they have really no advantage against these features which increase productivity in design really much.

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Hi Bit,

Thanks for sharing your experience.

I will have to discuss with our manager all this and see what he says on all that..

However what's interesting for me is the fact the you're recommending A2007 over A2010, as I was under the impression that 2007 was simply not stable and in 2010 they fixed many issues.

Re performance, perhaps it depends on how things are programmed, but as said, my experience is that when converting my MDB applications from previous versions to A2007 it becomes slower, it could be that the program needs to be re-designed in order to avoid that behavior, another possibility is that this only happens to mdb's linked to Access BE, while in ADP's you don't feel that difference, in either case its something that would require more research & testing until we come up with a final decision.

Perhaps I will open another post focusing on this topic where we can go into in more details.

Additionally I get a complete form in good looking design with a simple click where all prior versions looks like forms made in 1990
PS. If you can upload an screenshot of a form exploring this, something that cannot be done in 2003, that would be helpful in order to convince the manager.

Thanks again,
Ben
0
 
BitsqueezerCommented:
Hi Ben,

If you can upload an screenshot of a form exploring this, something that cannot be done in 2003, that would be helpful in order to convince the manager.

a video says more than 1000 pictures...;-)

https://www.youtube.com/watch?v=vBwGMpm9kuU   for creating a single form
https://www.youtube.com/watch?v=norAB9QvIls   for creating a continous form
https://www.youtube.com/watch?v=-AwKoX4U_lo with A2010, see how the layout view works

You'll find a ton of such videos around the web.

However what's interesting for me is the fact the you're recommending A2007 over A2010, as I was under the impression that 2007 was simply not stable and in 2010 they fixed many issues.

I'm working with A2007 now since 2008 (mainly ADP, but for some smaller projects also ACCDB) and I never needed to use the /decompile switch to repair a database file. Sometimes it was needed to import objects into a new created database file, but that's an old story with all versions of Access. I use A2010 (ADP) now for around 1 year and in the meantime I made a batch file which uses the "/decompile" switch because I need it often to repair a fully destroyed database file where not at least the VBA editor can be started without a full crash of Access and now import or export is possible anymore. In my personal opinion coming from my daily work with both versions I would say that A2010 was made to bring up a lot of new features very fast but was not tested enough so it more often crashes than earlier versions. I only use it because it's ADP works with SQL Server 2008 where A2007 ADP is only up to SQL Server 2005 (of course you can use newer SQL Server versions in both, but always with the risk that there are incompatibilities). The most of the new features doesn't work with ADP (like extended layouts, like new controls (i.e. the round buttons or the webbrowser control)) so there is no real advantage with this version for me anything else than working with SQL Server 2008.

performance, perhaps it depends on how things are programmed, but as said, my experience is that when converting my MDB applications from previous versions to A2007 it becomes slower, it could be that the program needs to be re-designed in order to avoid that behavior, another possibility is that this only happens to mdb's linked to Access BE, while in ADP's you don't feel that difference, in either case its something that would require more research & testing until we come up with a final decision.

In ADP there should be no performance difference as the file format is the same as A2003 ADP. But new features in newer database drivers don't need to improve performance, sometimes they are slower than before (i.e. if you can add transmission encryption which maybe was not included before, costs time).

In ACCDB/MDB the best recommendation is always to create a new blank database file in the newer version and then import the objects from the older one. It's also best to create some objects in the newer version than to import the older. Best example is the command button. If you import it directly from an older version it has the same layout and behaviour like in the old version. If you insert a new command button you'll see that it has the more modern style with enhanced graphics. You can't transform the old button into a new one. If you want to see the difference, try to insert both kind of buttons and then use Application.SaveAsText from the immediate window to export the form as text file, then look into the text file and compare the two buttons, you'll see that they have a different structure. So if that's true for a simple control you can be sure that it's also true for forms, reports, the database file itself. Access is (like any Microsoft product) made to work with older file versions nearly the same as before - but it is only a kind of "compatibility view" and no real conversion. And that costs time if the internal behaviour must be handled differently for older or newer objects. So if you build a new database file without importing old objects (except code because that's only code text) you will get less problems than importing older ones and go on working on them.
Changes can be done programmatically, i.e. you can import an old object manually (like a form) and then use some simple VBA loops to go through all controls of the source form (in design mode), create a new form and create the same properties like position or size or color and so on basing on the properties of the source form and the same with all controls of the source form. Is a little bit work to write such code once, but then can be used for any form and you get a new form with the same layout like the old but with all new kind of controls where you can add new properties (like the design properties in A2010) in the same code.

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Hi Bit,

From today's conversation with the manager I realized he is also concerned about MS Outlook that our users are heavily depended on.

Our IT guy does not want we should have two versions of Office in one pc, and this is why everyone here is currently using Office 2000 or 2003.

He claims that Office 2010 is capable of having a much larger pst file than previous versions, and this is a big advantage to him.

Let me know if you agree with that.

PS. re the Access upgrade, as mentioned I will really need more time to go over all aspects you have described, test them & decide what will best suit here our needs as we have 2 large systems, an ADP and a MDB that the BE is split between Access and SQL.

Thanks,
Ben
0
 
BitsqueezerCommented:
Hi Ben,

I cannot say very much about Outlook as I always tried to avoid that in private systems (I think that Outlook Express is better to use for me) and began to work with Outlook end of last year, Outlook 2013, so I'm really no specialist about that.

But what I can say is that Access is not an Office product. It is part of the Office Professional package and Microsoft does it's best to bring it to the "normal" Office user as a product like Word or Excel, but it's whole career since the first version is a separate one. Installations of Access does not influence the real Office package, the same thing with Office installations which does not affect an existing Access version. The reason is simply because it was an independent product in the first versions, not a part of Office and so it uses it's own methods and registry keys and so on. In newer versions it uses of course the libraries of Office to extend functionality. For example, I have A2007 and A2010 as full versions on my private computer, also O2007 and O2010 - they do not affect each other, I can work with both without problems, the only two which don't like to exist beneath each other are the two Access versions, whenever I want to start the other version it needs a minute to change installations in the background, I cannot start them both at the same time. But no one wants to have two Access versions normally other than we developers...:-)
One thing is of course true: An Office product installation will install the newest Office libraries and any program which uses them automatically updates internally to the newest version. That means: If you have, like me, two Access versions installed and try to develop a database with a reference to a 2007 Office library it will automatically be exchanged by the Office 2010 library - but not vice-versa. Means: If I deliver the A2007 database to anyone who has only A2007 it will not go back to O2007 library, it will simply say that there is a broken reference. So if you need to develop with different versions you better use a virtual machine and install only one complete Office product to avoid that. That's by the way only true for Office libraries, if you use an old DAO or ADO library it will not automatically switch to the newest version (so to come back to your performance problem: If you upgrade a DAO using database to ACCDB you should of course go to the references, throw away the old DAO reference and replace it with the new one which uses the new ACE engine, where the old one uses the JET engine).
The database engine is by the way always installed with Windows nowadays, so you normally already have at least the ACE 2007 if not A2010 engine.

But regarding "two versions of Office": I use Outlook/Lync 2013 on my workstation at work and also Office 2007 with A2007 full versions, they have no problem with each other. I'll get O2013 next times from our IT and still go on with A2007 which is no problem. The reason is: Access is the only product which can create an executable program using the Access Runtime which must be independent of any other program on the computer. You must be able to install a self-written database program on any computer even without any Office installation so Access will probably also go on to be a standalone product in future versions.
Outlook is also a product which is only bundled with Office but not dependent on an Office installation, that's the reason why I can use Outlook 2013 on my computer at work without influence of my A2007 installation (it does not change the Office library for example).

So if you still have Office 2000/2003 on the computers in your company I think your IT guy is also not "upgraded" to the newer versions in his mind...:-)
For older Office versions prior to O2007 it's true that the best recommendation was to not install two versions of Office on the same computer, but the times have changed and today they do no longer bite each other, see my double installation of O2007 and O2010, no problem, I can start and use any version of any product like I want.
I would say you should also consider to upgrade the whole Office products to O2007 at least because also the other products have so many improvements which makes life easier. For example, you can load a word file, click the drop down menu with designs and hover the mouse over the entries and you see directly which would be the result if you click on them live in the document, this is especially useful in Powerpoint, the favorite toy of managers. In Excel you have a powerful new conditional formatting which allows so many more settings, and it was made available beginning with A2010 also there so you are no longer limited to four settings in Access, and, and, and... I don't want to write an advertisement for Microsoft here...:-) But I personally see a lot of improvements to older versions prior to 2007, I would not want to work with older versions anymore - it takes some time to get used to the new interface, mainly the ribbon. But after working a short time with it you'll never want to go back to menus with submenus with submenus with submenus...:-)

For a real company usage it is of course the best way to first go through all departments and collect all existing applications in a list, then setup a new computer with new versions and install all these applications here and test if they work without problems. That does not only include real EXE files but of course also all documents using VBA programs, including all Access databases. If all the tests were OK it can be deployed to the users. That's what a responsible IT guy would do before upgrading anything.
In case of Access, where the users normally only get the runtime version, I'm pretty sure that there will not be any problem except the Access databases running on them. They need to be tested with a new runtime versions and normally you only need slight changes to get them run.

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Hi Bit,

Still waiting for opportunity to discuss with the manager your latest suggestion.

Just to mention again,
Many thanks for your store procedure suggestion, as this is working wonders, see attached.

"I would say, this alone paid off my annual subscription of EE!! the rest is a bonus.."
untitled.bmp
0
 
BitsqueezerCommented:
Hi Ben,

I'm glad that I could be of help for you.

Looks...interesting...:-)
A little bit "too much" maybe?... Maybe better to structure that in tabs or something similar?

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Hi Bit,

Perhaps you know the answer of the issue I'm having re this.

see below
http://www.experts-exchange.com/Database/MS_Access/Q_28672578.html

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Hi Bit, (My ADP genious!)

When you have a chance, please take a look at the following.

http://www.experts-exchange.com/questions/28937353/How-to-open-a-report-with-same-recordsource-as-form-in-ADP.html

Thanks,
Ben
0
 
BitsqueezerCommented:
Hi Ben,

I didn't had a chance, it's already deleted....:-)
By the way: You can also send me a personal message, you do not need to add a comment to a closed thread.

Cheers,

Christian
0
 
bfuchsAuthor Commented:
Hi,

I had found a solution and forgot that had sent you earlier a request to look for it...-:)Sorry

You can also send me a personal message..
Good, will do that next time.

Thanks,
Ben
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.