 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / May 2007Different @@SPID for same user/connection ?!?!?!
Hi! I am currently writing an ACCESS ADP app which uses SQL Server 2005 Express Edition. users have to login to the app/database and I am setting the current application-connection: Application.CurrentProject.OpenConnection strConnect, strUser, strPassword I am also using @@SPID within my tables/stored procedures to enable users to have specific data. The recordsources of my forms use SQL-server views/UDFs, I am modifying data within my database via stored procedures. I am now running into a problem with the @@SPID, because one of my forms uses a different @@SPID than the others and my user-specific approach doesnt work... how can this happen ? somehow access seems to use a different/new connection for this forms recordset than Application.CurrentProject.Connection how can I work around this ? any help with this would be greatly appreciated, thanx in advance! regards, Hans Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same connection to retrieve the data source of a subform." - http://support.microsoft.com/kb/308312 What is the effect you are trying to acheive by using @@SPID? Regards, Malcolm > Hi! > [quoted text clipped - 32 lines] > regards, > Hans Hi Malcolm! > Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same > connection to retrieve the data source of a subform." -http://support.microsoft.com/kb/308312 > What is the effect you are trying to acheive by using @@SPID? thanx for your reply! yes, I am using subforms and I realized that sometimes my subformy use a different @@SPID than the main form, but this doesnt happen with all my forms. the main-form is usually used to scroll/create/delete records, the sub-forms display the contents of a specific record. I am using (temporary) tables where I load a record into, when a user edits it. my sub-forms are bound to this temporary tables. after he finished editing I update it in the real table if he wants to save the changes. I am using views to bind the content of a user-specific record to my form, in this view I use @@SPID, also in the stored procedures which I use to load/save records. therefore I have to store a specific ID for every user within the records of my temporary table, to correctly edit/assign user-specific data. do you have any idea how I can successfully accomplish this ??? regards, Hans Hans, I've never taken this approach. Do you create the temp tables "on the fly"? If so, you could create temp tables named after the current user? If not, why not cache the top-level @@SPID in a program variable and pass it into all your stored procs/view that create/load/move the records? I really don't understand your application well enough to make reasonable suggestions.... Good luck. At least now you know why you were getting different values for the @@SPID. Oh, you also might consider, instead of having subforms, to have two main forms and make the 2n'd be dependent upon the 1st (i.e. in the "on current" of the 1st, set a hidden form variable which is used to parameterize the query/stored proc of the 2nd form). Malcolm > Hi Malcolm! > [quoted text clipped - 30 lines] > regards, > Hans I'm really not sure that I agree with this fully > Hans, > [quoted text clipped - 56 lines] >> regards, >> Hans Everyone please note that Aaron Kem.pf is attempting to impersonate one of our regular posters again. Tom would never post such a message. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > I'm really not sure that I agree with this fully > [quoted text clipped - 58 lines] >>> regards, >>> Hans Hi Malcolm! > I've never taken this approach. > Do you create the temp tables "on the fly"? [quoted text clipped - 6 lines] > upon the 1st (i.e. in the "on current" of the 1st, set a hidden form variable which is used to parameterize the > query/stored proc of the 2nd form). I dont use the sql-server bulit-in temp tables (#table, or ##table), instead I am using regulary tables which exist within the database and I just "missuse" them as temporary tables. I think that it also can be possible that the same user (login) is logged in more than one time, so I cant use the approach with the current user name. I think I will follow Your suggestion and either chache the top-level @@SPID or use a special table/stored procedure which returns an unique number after login, which I then use within all my stored procedures. I alos will have to replace the views with UDFs. I didnt understand Your suggestion with the 2 main-forms ? having 2 main-forms will lead to two seperat windows, but I need to dispaly all record-specific data within one window. Thanx for Your suggestions! regards, Hans Hans, > I didnt understand Your suggestion with the 2 main-forms ? > having 2 main-forms will lead to two seperat windows, but I need to > dispaly all record-specific data within one window. I think you DID understand my suggestion. Whats the matter with two (synchronized) windows? Aesthetics? Anyway, good luck with top-level @@SPID approach (let us know how it works!). Malcolm Hi Malcolm! > Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same > connection to retrieve the data source of a subform." -http://support.microsoft.com/kb/308312 > What is the effect you are trying to acheive by using @@SPID? thanx for your reply! yes, I am using subforms and I realized that sometimes my subformy use a different @@SPID than the main form, but this doesnt happen with all my forms. the main-form is usually used to scroll/create/delete records, the sub-forms display the contents of a specific record. I am using (temporary) tables where I load a record into, when a user edits it. my sub-forms are bound to this temporary tables. after he finished editing I update it in the real table if he wants to save the changes. I am using views to bind the content of a user-specific record to my form, in this view I use @@SPID, also in the stored procedures which I use to load/save records. therefore I have to store a specific ID for every user within the records of my temporary table, to correctly edit/assign user-specific data. do you have any idea how I can successfully accomplish this ??? regards, Hans woudln't it just be easier to change to SUSER_SNAME() instead of SPID? I just don't think that I necessarily believe with the diagnosis that a simple ADP application uses a second SPID I've never been able to duplicate this anyone got a test case to duplicate this?? > Hi Malcolm! > [quoted text clipped - 32 lines] > regards, > Hans Everyone please note that Aaron Kem.pf is attempting to impersonate one of our regular posters again. Tom would never post such a message. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > woudln't it just be easier to change to SUSER_SNAME() instead of SPID? > [quoted text clipped - 42 lines] >> regards, >> Hans Hi Tom! > woudln't it just be easier to change to SUSER_SNAME() instead of SPID? > I just don't think that I necessarily believe with the diagnosis that a > simple ADP application uses a second SPID > I've never > been able to duplicate this > anyone got a test case to duplicate this?? it doesnt happen with all forms. my forms usually have a main-form with different sub-forms. I am using different main-forms to display different record-types within my database. on one of my main forms I get another @@SPID as on a sub-form on it. regards, Hans Sorry. You've been attacked by the resident troll masquading as Tom. Please don't expect him to answer back. The troll's intention is not to help you or anyone else, only to disrupt the Microsoft newsgroups. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > Hi Tom! > [quoted text clipped - 13 lines] > regards, > Hans it doesn't happen with all forms well I think that i may have seen this with reports also either way, I ran into this same problem recently; changed to SUSER_SNAME for filtering/default instead of SPID and it worked like a charm just change the grain of your where clause > Hi Tom! > [quoted text clipped - 13 lines] > regards, > Hans Everyone please note that Aaron Kem.pf is attempting to impersonate one of our regular posters again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > it doesn't happen with all forms > [quoted text clipped - 4 lines] > > just change the grain of your where clause Hi Tom! > well I think that i may have seen this with reports also > either way, I ran into this same problem recently; changed to SUSER_SNAME > for filtering/default instead of SPID and it worked like a charm > just change the grain of your where clause this doesnt work either, because the same user can be logged in more than once. I am now using the approach to store the top-level SPID after login and provide it as a parameter to all my stored proc/UDFs. regards, Hans Hi Malcolm! > Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same > connection to retrieve the data source of a subform." -http://support.microsoft.com/kb/308312 > What is the effect you are trying to acheive by using @@SPID? thanx for your reply! yes, I am using subforms and I realized that sometimes my subformy use a different @@SPID than the main form, but this doesnt happen with all my forms. the main-form is usually used to scroll/create/delete records, the sub-forms display the contents of a specific record. I am using (temporary) tables where I load a record into, when a user edits it. my sub-forms are bound to this temporary tables. after he finished editing I update it in the real table if he wants to save the changes. I am using views to bind the content of a user-specific record to my form, in this view I use @@SPID, also in the stored procedures which I use to load/save records. therefore I have to store a specific ID for every user within the records of my temporary table, to correctly edit/assign user-specific data. do you have any idea how I can successfully accomplish this ??? regards, Hans Hi Malcolm! > Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same > connection to retrieve the data source of a subform." -http://support.microsoft.com/kb/308312 > What is the effect you are trying to acheive by using @@SPID? thanx for your reply! yes, I am using subforms and I realized that sometimes my subformy use a different @@SPID than the main form, but this doesnt happen with all my forms. the main-form is usually used to scroll/create/delete records, the sub-forms display the contents of a specific record. I am using (temporary) tables where I load a record into, when a user edits it. my sub-forms are bound to this temporary tables. after he finished editing I update it in the real table if he wants to save the changes. I am using views to bind the content of a user-specific record to my form, in this view I use @@SPID, also in the stored procedures which I use to load/save records. therefore I have to store a specific ID for every user within the records of my temporary table, to correctly edit/assign user-specific data. do you have any idea how I can successfully accomplish this ??? regards, Hans Hi Malcolm! > Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same > connection to retrieve the data source of a subform." -http://support.microsoft.com/kb/308312 > What is the effect you are trying to acheive by using @@SPID? thanx for your reply! yes, I am using subforms and I realized that sometimes my subformy use a different @@SPID than the main form, but this doesnt happen with all my forms. the main-form is usually used to scroll/create/delete records, the sub-forms display the contents of a specific record. I am using (temporary) tables where I load a record into, when a user edits it. my sub-forms are bound to this temporary tables. after he finished editing I update it in the real table if he wants to save the changes. I am using views to bind the content of a user-specific record to my form, in this view I use @@SPID, also in the stored procedures which I use to load/save records. therefore I have to store a specific ID for every user within the records of my temporary table, to correctly edit/assign user-specific data. do you have any idea how I can successfully accomplish this ??? regards, Hans Hi Malcolm! > Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same > connection to retrieve the data source of a subform." -http://support.microsoft.com/kb/308312 > What is the effect you are trying to acheive by using @@SPID? thanx for your reply! yes, I am using subforms and I realized that sometimes my subformy use a different @@SPID than the main form, but this doesnt happen with all my forms. the main-form is usually used to scroll/create/delete records, the sub-forms display the contents of a specific record. I am using (temporary) tables where I load a record into, when a user edits it. my sub-forms are bound to this temporary tables. after he finished editing I update it in the real table if he wants to save the changes. I am using views to bind the content of a user-specific record to my form, in this view I use @@SPID, also in the stored procedures which I use to load/save records. therefore I have to store a specific ID for every user within the records of my temporary table, to correctly edit/assign user-specific data. do you have any idea how I can successfully accomplish this ??? regards, Hans Hi Malcolm! > Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same > connection to retrieve the data source of a subform." -http://support.microsoft.com/kb/308312 > What is the effect you are trying to acheive by using @@SPID? thanx for your reply! yes, I am using subforms and I realized that sometimes my subformy use a different @@SPID than the main form, but this doesnt happen with all my forms. the main-form is usually used to scroll/create/delete records, the sub-forms display the contents of a specific record. I am using (temporary) tables where I load a record into, when a user edits it. my sub-forms are bound to this temporary tables. after he finished editing I update it in the real table if he wants to save the changes. I am using views to bind the content of a user-specific record to my form, in this view I use @@SPID, also in the stored procedures which I use to load/save records. therefore I have to store a specific ID for every user within the records of my temporary table, to correctly edit/assign user-specific data. do you have any idea how I can successfully accomplish this ??? regards, Hans Hi Malcolm! > Do you have subforms? If so, then heed these words: "Unlike with other database objects, Access does not always use the same > connection to retrieve the data source of a subform." -http://support.microsoft.com/kb/308312 > What is the effect you are trying to acheive by using @@SPID? thanx for your reply! yes, I am using subforms and I realized that sometimes my subformy use a different @@SPID than the main form, but this doesnt happen with all my forms. the main-form is usually used to scroll/create/delete records, the sub-forms display the contents of a specific record. I am using (temporary) tables where I load a record into, when a user edits it. my sub-forms are bound to this temporary tables. after he finished editing I update it in the real table if he wants to save the changes. I am using views to bind the content of a user-specific record to my form, in this view I use @@SPID, also in the stored procedures which I use to load/save records. therefore I have to store a specific ID for every user within the records of my temporary table, to correctly edit/assign user-specific data. do you have any idea how I can successfully accomplish this ??? regards, Hans |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.