Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 7th, 2006, 01:34 PM
Authorized User
 
Join Date: Sep 2006
Location: West Bloomfield, MI, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default MS Access .mdb calling SQL server 2000 stored proc

background:
I am converting Jet SQL queries to SQL server stored procedures. I don't have access to actually create the procedures. I am converting the queries and sending them to the sql DBA to store and run. I wrote pass-through queries to access the stored procedures. In the connect string property, I pass parameters like ODBC, server=, database=, UId=, Upw= and my the cboboxes in the forms work.

Now, this solution works for me while I'm in test mode w/ my Uid and Upw in the connect string of the pass-thru qry. But I'm confused as to how to implement the solution on the actual production db for the rest of the users of the db. Will I have to code the pass-thru qry's for each user who accesses the db? How should I implement the solution?


Reply With Quote
  #2 (permalink)  
Old September 8th, 2006, 07:06 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You can use either Windows NT authenitication on the SQL server, which will take the users' credentials from their login credentials. You can either add each user individually to the Users list after you add them to the Server Logins, or you can create a group for them in AD and then add the group as a user and login on the server. This last is the easiest.

Alternatively, you can use SQL server authentication and then create a generic account that your users will login with when they open the database. This login is persistent, so no additional login information needs to be sent as the user uses the application.

I frequently use the second alternative since we still use NetWare, if you can believe it, and then I log the users' transactions by taking their login name from a Function in a Module:

'-----
Function sUser()
sUser = (Environ$("Username"))
End Function
'-----

Then I just log sUser when a record is created or modified. This alleviates the problem with generic SQL Server authentication.

(To explain further, If you use an Access adp file, then SQL forces the login when you open the application. If you use an mdb file, I create a main form and then hide a field on it from the SQL server so that SQL forces the login when the first form is opened, and not when the first transaction is attempted, which can confuse the user.)

Did this help?


mmcdonal
Reply With Quote
  #3 (permalink)  
Old September 8th, 2006, 08:22 AM
Authorized User
 
Join Date: Sep 2006
Location: West Bloomfield, MI, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes mmcdonal, thank you very much. I appreciate the different options. I like the last option best because it is simplest.

On this same topic re: sql server stored procedures, I understand that the stored procedures will be stored on the server, compiled and waiting to be called by a user thus making response time more speedy rather than the present state of using jet sql and linking to get the information. I'm having a problem understanding and visualizing the process:

1. Does the qry call refresh the data? e.g. if 2 users login at 8AM, the 1st user enters the db and accesses a form w/ the stored proc. call and views the data on the screen. A bit later, the 2nd user modifies the same table that the 1st user referenced. Then a bit after that, the 1st user references the same form w/ the lstbox referencing the updated table. What will happen in the backend?

I would guess that the ODBC connection string will have to be re-established when the reference is made to the stored procedure on the sql server. Is my guess right? Please talk about this to clarify?

Fanar

Reply With Quote
  #4 (permalink)  
Old September 8th, 2006, 08:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

SQL Server will manage the transactions and record locking. It is always better to use server side queries (which are dynamic and show the state of the data when called) since SQL server has query optimization and this can prevent large amounts of data off the wires. If you used a client side query, ALL the data necessary for the query has to be brought over the wire, and then sorted and filtered on the local machine. Not good.

Anyway, queries are dynamic datasets, so they use the most current data. They will refresh forms or reports, but they don't refresh the data per se. Only committed transactions update data (not refresh it).

1. What happens on the back end is that transactions are logged, and then committed if they can be accomplished successfully, otherwise they are rolled back. The stored procedure should call only committed transactions.

The ODBC connection string... I think you mean to say you are using an ODBC data connection and not any other sort, like ADO (which is a good quick one for going from Access to SQL, but not as solid as ODBC, which is slower) anyway, the connection can be established and then reused as needed. It is always better to use as few connections as possible. One if you can get away with it. But once the user enters their SQL server login credentials when they open the app, they will not (or should not) be prompted to log in a second time while the app stays open. Is that what you mean?



mmcdonal
Reply With Quote
  #5 (permalink)  
Old September 8th, 2006, 10:06 AM
Authorized User
 
Join Date: Sep 2006
Location: West Bloomfield, MI, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Please exuse my ignorance, I'm new to the forum arena. I just added more input to this conversation but I forwarded the reply from mmcdonal to his email rather than the forum. Sorry about that.

mmcdonal, could you please somehow post my new set of questions to the group?
Thank you

Reply With Quote
  #6 (permalink)  
Old September 8th, 2006, 10:22 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I did not receive an email from you. Can you repost?


mmcdonal
Reply With Quote
  #7 (permalink)  
Old September 8th, 2006, 11:32 AM
Authorized User
 
Join Date: Sep 2006
Location: West Bloomfield, MI, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I am using an ODBC connection. I'm currently logging in w/ a uid and upw which gives me access to a bunch of server drives and one of the drives have the DB that I'm working on, on it. I open the DB and do my conversions. I had to set up a DSN to point to the test server. I do not Know whether I'm logging into the sql server. I don't think so. I don't believe I have access to it. I'm linking to it for the data. I'm doing the jet sql conversions and sending them to the sql server DBA to actually store the code as stored procedures and run them.

My initial login is to the servers to access the db but not the sql server, that's being done via linking to the tables.

When I first tested a pass-through qry, I did not populate the full connection string w/ everything like: server=, db=, uid= and upw=, just "ODBC" and the connection failed. When I supplied all the parameters in the connection string property than the qry ran. So I'm guessing that's why I had to supply all those parameters. Is this a valid assumption?

If I understand what you're saying in your prior response above, the connection to the sql server only has to be made once per user at the time of entering the application. Than the number of references to pass-through queries is irrelevant. The forms have many list and cboboxes to populate, each of those will be converted to a pass-through query.

How do I ensure that a procedure is committed?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database migration MS Access 2003 to MS SQL 2000 ayazhoda SQL Server 2000 3 April 23rd, 2007 11:38 AM
Calling Stored Proc with OUT params nkrust ASP.NET 2.0 Professional 2 April 19th, 2007 12:31 AM
Calling an insert stored proc from a select stored dzitam SQL Language 10 April 2nd, 2007 12:39 PM
Read SQL Server 2000 files into MS-Access slewis4 SQL Server 2000 8 March 1st, 2006 04:21 PM
Calling a T-SQL Stored proc from DTS mepancha SQL Server 2000 1 April 13th, 2005 09:12 AM



All times are GMT -4. The time now is 04:56 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.