Authentication Problem To MS SQL SERVER From MS ACCESS App
Jul 12, 2005
Hi.
I recently created SQL SERVER backend for my MS ACCESS app. (I upsized ONLY the tables, so the ACCESS app contains linked tables).
The SQL SERVER resides on a different machine than the MS ACCESS app.
Every time that I run the ACCESS app, it fails to connect to the SQL SERVER ("Login failed for user 'xxx'") and it prompts me for user name and password.
After I give it the right user name and password, no probs.
The problem is that ACCESS doens't save the new user name and password.
So every time that I run the app, again, it uses the wrong default user name and password (I don't know where it gets it from), and I need to re-enter the correct one.
For a databse which I am going to create, I would like to have an authentication system which utilises the database users which I specify, however instaed of using a password the idea would be to use a fingerprint scanner to logon. T^his would be for an A - Level IT project and I would seriously like to know how to integrate biometric security into a database. If anyone knows how to do this please post CLEAR instruction possibly with some SCREENSHOTS if possible.
After learning that 2007 has no User Security roles, and not having Sharepoint or a SQL server, I decided to work starting with Bob's Simple Login script located here (http://www.btabdevelopment.com/main/AccessSamples/tabid/54/Default.aspx).
I've got it functioning fine and incorporated some of the options also made available here (http://www.databasedev.co.uk/login.html).
You'll see the code below used to store info in a hidden form that is holding the username and permissions level. I'm looking to try and store this information into a global variable instead of a hidden table.
I know that I could define it as a variable right here in the code, but how do I define it as a Global variable so I can use it later in the application in the VBA?
Private Sub cmdLogin_Click() Dim strUser As String Dim strPWD As String Dim intUSL As Integer
strUser = Me.txtUser If DCount("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'") > 0 Then strPWD = DLookup("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'") If strPWD = Me.txtPwd Then intUSL = DLookup("[SecurityGroup]", "tblUsers", "[UserName]='" & Me.txtUser & "'") Forms!frmUSL.txtUN = strUser Forms!frmUSL.txtUSL = intUSL Select Case intUSL Case 1 DoCmd.OpenForm "frmHome", acNormal Case 2 DoCmd.OpenForm "frmHome", acNormal, , , acFormReadOnly Case 3 MsgBox "Not configured yet", vbExclamation, "Not configured" Case 4 MsgBox "Not configured yet", vbExclamation, "Not configured" End Select DoCmd.Close acForm, "frmLogin", acSaveNo Else If MsgBox("You entered an incorrect password" & vbCrLf & _ "Would you like to re-end your password?", vbQuestion + vbYesNo, "Restricted Access") = vbYes Then Me.txtPwd.Value = "" Counter = Counter + 1 If Counter = DLookup("[OptionValueNum]", "tblOptions", "[OptionsID]=1") Then MsgBox "You have entered an incorrect password too many times. This database will now close!", vbCritical, "Wrong password!" DoCmd.Quit End If Else DoCmd.Quit End If End If End If End Sub
Hi All, Im a beginner in Access.I created 2 tables-First one has the User-ID , Password and other details, Second table has All the personal details of those users.I have created a form with 2 text boxes and a button. The User enters his ID and Password and "Submits" it. I want to check his User-ID , Password with the data present in the table and if that is correct- display another form with all his details... How do I do that..?? please help me with this... Thanks in Advance... bye.
We're currently looking at changing a couple of my more complex Access 2003 databases to Access Front End with a SQL Server Back End. This should give us better stability and reduce network traffic loads. I was looking at the Access help file on Upsizing and came across the following paragraph. On face value to a novice like me this would seem the easy option - but is it?
"Create an Access database front-end to an SQL Server database back-end so that you can create a front-end/back-end application.
This approach requires very little application modification since the code is still using the Jet database engine. "
I am importing an Excel file into a table in SQL Server that was created by an AS400 program.
I linked this table in MS Access. The date fields from the SQL Server table are split out. DSREQM is the month and day. DSREQY is the year. When opening the table it looks like this:
DSREQM DSREQY 215 6
I need to be able to convert this is a field to 2/15/06. However I cannot get this to work. For some reason when I execute the query it comes out as 8/02. Here is what I have:
I am attempting to write an application, using Access2003 as the front end. The Backend May be either Jet (ie an MDB), or SQL Server.
I am trying to make it so that the same code works regardless of the back end.
My approach has been to set the forms recordset to an ADO recordset like so:
Set Rs = New ADODB.Recordset 'Rs.CursorLocation = adUseClient Rs.CursorLocation = adUseServer Rs.Open strSQL, gcn, adOpenDynamic, adLockOptimistic ' Set Me.Recordset = Rs
Anyone done this, and willing to sahre information/pitfalls.
First problem I am encountering is that Jet only seems to like .CursorLocation = adUseClient, and SQL Server only seemns to like .CursorLocation = adUseServer
Hi to everyone Im not sure which thread i should be in, but here goes :confused: i want to create a members database for members on my forum with HTML! Ive been told to make the database with ACCESS ive played for 2 days and im now acctually getting somewhere, i would like members to interacte with it ie....add there own details to it. How do i do this and how do i get it to my server, well i did upload a tester but no one could see it and its says they need to have MS installed or something like that :eek:
I'm trying to help someone with Access on a CITRIS Server. I'm working with them VIA email. They say they're having problems communicating with printers and would like to utilize their email with Access. Can someone fill me in on common problems using Access with CITRIS Server as far as printers or email or anything else?
To be perfectly honest, I know nothing about CITRIS servers. Is CITRIS a hardware system or computer type of is it an operating system???
I need to be able to add a feature to an Access project that will select a group of records based on an area code( user will have to be able to select a desired number like 100, or 1000), mark those as selected, and create a single csv file...The area codes are stored in the SQL SERVER database...right now they just export groups of #'s based on area code to excel select the amount they want and cut and paste into a flat file for another application to use...they would like to be able to select a number from a list box or type it in...like 100 ( which means take a 100 #'s from all different area codes) then have them automatically noted in database as being selected..and then export all selections to one csv file... should I do part of the work in the Sql Server behind the scenes..the format of the csv file also needs to be specific in that the first row is the columns separated by commas and then the data follows all text fields enclosed by qoutes.. Thanks for anyone's help ..Im very inexperienced with VB and Access
I have created an adp, and have created stored procedures for all of my forms. In the SQL Server database I have given my users permissions as db_datareader and db_datawriter, however they were unable to open any of my forms because they didn’t have execute permissions on my stored procedures. I finally had to give them permissions as db_owner so they could use my forms. Do I have to give them db_owner permissions to execute my stored procedures? I know I can go in and assign them execute permissions for my stored procedures, but there has to be a better way. Does anyone know how to give users execute permissions on stored procedures without having to give them db_owner permissions or having to grant execute permissions for each stored procedure individually?
I am still in the development phase of my project and having to assign users or groups with each stored procedure I create could really be a pain. Can someone please help me?
I am accessing a SQL Server 2000 database from MS Access. While I can view the tables and can add records, I cannot edit or change them. How do I get around this?
I am trying to build a form to make deletes from our sql database easier for some of my coworkers.
They have a combo box that they select a patient name from, then based on that patient id, they have a second combo box that shows all appointments that patient has been to. All right so far, yes?
I want, after the first two selections are done and we have a patient and an appointment selected, to have a textbox that returns the count of the number of medical documents based on that appointment id.
I am trying to use a text box for this. Code: "select count(appt_id) from template_audit where appt_id = " & Me.cboAppointment
I don't know what property of a text box to use to return a single sql statement into it. Anyone have any ideas?
The database that I have on MS Access has tables, queries, forms (VB code), reports and functions (VB). I could migrate the tables and queries through the Enterprise Manager. I want to have the forms and reports in html. Is there any software tool that can help me with over all migration?
I've create an ms access security workgroup and put .mdb and .mdw file to a folder under any acount on a server. When these files in my local machine it work properly. But when these files on server I myself cannot open .mdb file. How to make ms access security on a server ?
I know and have worked with MS Access for over 3 years now. Just would like to know if there is very much difference between MS Access and SQL Server. Are they similiar or are they completely different . In terms of designing forms, reports, sql.
Appreciate some feed back and also where I could view or look at sql server.
I have a question of the connection between SQL Server and MS Access.
I have a project, which is using SQL Server as backend, we use MS Access forms as the frontend.
Firstly, I created the datbase (db1) in the SQL Server, and create a few tables in db1. And then, I create a few forms in the MS Access, I used ODBC connection to link tables from SQL Server to MS Access, it is working fine. Those forms can access the linked tables.
After it is done, I put the MS Access database in the sharing drive, many people can share the files in this sharing drive.
When I go to my associate's computer, and then open the MS Access database in the sharing drive. The forms is working, but, it cannot access to linked tables from SQL Server.
I go back my computer again and then open the MS Access database, it is working fine.
What's the problem with it? Does anybody need to install SQL Server software and also have db1 database in SQL Server and the path?
I want any user can access the linked tables from SQL Server, but I do NOT want any user can open the SQL Server and open the tables directly. I just want the users can access the linked tables through MS Access Forms only. Because I will make security in the MS Access. So, I don't let the users modify/open the tables in the MS Access, the users must access the tables via the forms only.
Hi, Using MS Access on a Server on the web with main programming in .asp I have done all the hard work to have a userid logon with password and stop anyone getting in. I have now set up a change password facility and that again is working great on checking userid, old password, new and repeat new password. I cannot get the update part working to update the new password. It is in the way I have specified the "call adorecordset.open" as that is the line I get the error as incompatible parameters.
Basic Code is :
set adoconnection = server.createobject("adodb.connection") set adorecordset = server.createobject("adodb.recordset") connectionstring = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & server.mappath(databaseid) & "" & databaseid & "user.mdb"
I am a computer novice with no experience in PHP or SQL Servers in general. Is there anyway to set up Access as a front-end to an MS SQL Server without having coding knowledge? Is there any good wizard that will export data/formats to a SQL Server while still keeping the interface mechanisms of Access?
We have recently changed our SQL Server and I am now trying to point an access database to this new server using ODBC connections.
I have updated my ODBC settings and altered the name to the new object. It stills seems as though the database is pointing to the old tables though as receiving an error message:
"The microsoft jet database engine could not find object....."
*I upsized ACCESS 2002 (tables only) to use SQL SERVER 7.0 as backend. (linked tables). * Almost all the ACCESS forms are bound type forms.
Now, I have two problems: 1. Other applications (not ACCESS ones) that use different dbs on the same SQL SERVER , are slowed down / halted. 2. My SQL SERVER license is limited to 8 "user counts". When there are 8 instances of the ACCESS app, any additional connection request is refused (from any app). (I can't afford buying more "user counts").
I suspect that beacuse the forms are bound, it hold connection open as long as the form is used. Also, each ACCESS app instance, opens its own connection.
The solution that I see, is to use unbound forms(disconnected), and opening the connection in code using connection pooling. Would you recommend on this solution? do you have any suggestions?
I have an Access database on my server. My client does not like the idea of having to download the db, make changes, then upload it back. In the past, he has dealt with SQL databases, where you can create an access data project, that will give you a desktop shortcut straight to the database. I was wondering if there was any way to do something similar with an Access database. There is FTP setup for the site and all required permissions are setup. Is there any way to create a desktop shortcut, so he can edit the database in real-time, without having to download and upload? Thanks for any help.
I have a SQL Enterprise server that I am using a MS Access to remotely access and populate the tables from my laptop into a database.
Based off of the linked data between my project and the true SQL database, I have created some Access Data pages for interacting with the data.
What I need to know how to do is how do I modify the Access Data Pages (source code), so that instead of using my local access project file for source of data it points directly at the SQL database and can operate on the web only interacting with the SQL not my computer anymore.
---Greater detail if I didn't do a good job with the description above---
SQL Server A has database 'Helpdesk'.
IIS Server B for webpages (.aspx).
Computer B has a .ADP (Access Data Project) called 'helpdesk.adp' which is linked via SQL username and password to Server A's 'Helpdesk' database.
Computer B has several Data Access Pages that are used to manipulate the database data.
Problem1: Convert Data Access Pages to normal web pages (.aspx) on Server B so that they talk directly to Server A's 'Helpdesk' database via SQL username and password and no longer care about any reference to Computer B?
Problem2: What functionality from Data Access Pages can not be carried over to a (.aspx)?
I not a big time DB person so please be gentle. I am a computer technician at a school district and need some help. We use a program called Aeries made by Eagle Software for out attendance and grading. It uses an interface that runs through Microsoft Access. There are two version of this software. One is the SQL version and the other just uses an Access DB on a server. We have the Access DB version running Access 2003. It was explained and accepted in the beginning that we didn’t really need the SQL version because of our district size which is some where around 1500 students. Our district consists of 6 school sites that are all connected with a fiber optic backbone running at 200mb with a minimum of 100mb to each desktop. We have a DB for each site running on 1 very beefy server in a central location. I’ve run my problem by the software maker and don’t feel like I’m getting much help. The problem I am having is slowness during login. When the first user of the day attempts to login the program is fast, about 10 seconds from “ENTER” to the main menu. As soon as that 1st user clicks one of the menu buttons anyone else who attempts to login to that database after that experiences at least 30 seconds from “ENTER” to the main menu. I’ve watched what files are accessed and when. I see that when the 1st user logs in, the systemxp.mdw file is accessed and a systemxp.ldb file is created. Then when the user clicks a button on the main menu, the sch05xxx.mdb is accessed and a sch05xxx.ldb file is created. The sch05xxx.mdb file is the main database file for any particular site. The “xxx” would be the site number. As soon as that file is accessed or that lock file is created the user will experience the long login times and sometimes as long as 4 minutes. I don’t believe this is a hardware problem. During the same time that users are slow I have made a copy of one of the databases onto the same HDD on the same server and as long as I’m the 1st person to open it, it will fly. The 2nd person is slow just like the rest. I also don’t think it’s a permission issue, at least not in the domain. I’ve run all these test logged in as myself and have Domain Admin rights to everything. I believe this is a “split database” with the tables on the server and the forms, and queries on the users computer. I read an article here, http://www.microsoft.com/office/previous/xp/columns/column05.asp called “Why is Access 2000 slow for more than one user?” Has anyone else had this problem or have a solution? If I’ve missed something and you need more info please let me know.Thank you for your time and I appreciate any responsesJohn
I am looking to upsize a Access BE database to SQL Server. The FE database will still be MS Access. I would like to keep the FE as it is and use an ODBC connection to the SQL BE database. My problem today is that I need better performance since we are using the database over a WLAN connection. As I understand it, Access sends the entire dataset/table across the connection whilst SQL Server only returns the requested data.
Is there anyone familiar with SQL Server that can guide me in the right direction here. Any insights are most welcome and appreciated.
TWe aare using computers with WinXP, server running Windows 2003 Server
