Im just starting to build my first application in Access 97 Pro.
What im trying to build is a helpdesk package hopefully good enough to replace the old one that we are currently using. (tall order there!)
To control access to the application, im trying to build a login system that can use the network domain login, thus allowing users to login using their network username and password (this'll help when they change their password every x amount of days, and only give them one to remember).
From there, i also want to have a security system in place that will allocate the users to a specific group i.e. System Administrator, Helpdesk Analyst, Helpdesk Manager etc.
Can anyone suggest a method on how to code in the network login part? I was thinking on coding a module, and providing references on the appropriate button (for logging in).
Due to being menu driven, is there a way to make certain items active/inactive depending on their user group allocation???
First things first... how many users are you going to be dealing with here? Addressing multi-user environments can be a bit trickier then you may think using only access. Is this going to be a front end for a different db system, or a stand-alone product?
To address the actual question, check out this bit of code:
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
This basically gives you access to the function "GetUserName" which you could simply call with:
User = GetUserName()
The "proper" usage would be:
strUser = String$(15, " ")
lngBufLen = 15
IsUser = GetUserName(strUser, lngBufLen)
If IsUser Then
ntUser = Left$(Trim(strUser), Len(Trim(strUser)) - 1)
Just keeping this in access for the time being, but im haveing a bit of a problem with the login screens.
What i've done is created an administration section which will be hidden to all users, and will be password protected. this is where the administrator can add new users to the login table (has the fields ID, username, password). This table populates the login form (users can select their username from a combo box, but must type in thier password.
The problem im having is getting the database to compare the password typed in the password text box with the password logged in the login table (so that way it doesn't just pick up any old password.)
here's the code that i've used so far:
Private Sub login_button_Click()
'Check to see if a username is selected
If IsNull(Me.login_select) Or Me.login_select = "" Then
'Check to see if data is entered into the password box
If IsNull(Me.password) Or Me.password = "" Then
'Check value of password in tblEmployees to see if this
'matches value chosen in combo box
'CODE GOES HERE
'Close logon form and open splash screen
DoCmd.Close acForm, "test_login", acSaveNo
can anyone tell me what code i should use in the 'CODE GOES HERE part? thanks for your help!
Be aware that you are quickly going to be getting into some very database-specific code. I agree that you should first evaluate how well MS-Access will turn out to be suited to this job, before you rig the security-part. (Also evaluate seriously just how secure the data will actually be.)
Microsoft SQL Server is, of course, not the only server that you can choose from, but even it is not "outrageously" expensive. If an SQL server (of any kind) is called-for, the time to make that determination is now, not later. Because the cost of your time, spent doing it one way only to then re-do it, is far greater than the cost of an SQL Server license!
Build it un-secured, test it then pressure-test it, then secure it and load it with live data. Right now, "you have seen a rabbit, and you are chasing it." But if it's the wrong rabbit you are wasting time and money. (Specifically... if the SQL server in question can take its login-information from the Windows system, as many can, then all this investigation and trouble will be ... pointless. From a financial standpoint, "scrap." You'd be laboriously building something that won't enter or remain in production to justify its cost.)
Ok, thanks for that. I'll take this time to explain what im doing is im building a "tester" database and interface in access 97, just to check that i can get the rough functionality working properly.
From there im going to try and build a stand-alone application using VB.net 6 professional (don't really get decent software to use here at work). Because there's only going to be up to 8 people at most using the application, that's where my initial question came in about logging into the system using their domain login details.
I know this is the expensive and time consuming way where i'll be "re-inventing the wheel" a couple of times, but at the moment it's still in the idea's stage and im trying to get off paper into a form of "working example".
ok, ignoring the login system for just now, i am trying to implement the following, in brief:
Highly flexible asset management system
This will involve managing manufacturer details, model details, location details. From there, information about each individual asset i.e. serial number, article number etc. Each asset will also have its own individual history log (to monitor where the asset goes if it moves around different sites frequently).
Advanced call request system
This involves using a pretty much standard call request system, but this will allow documents and multiple assets to be attached to a single report. It will also allow for different issues of a specific call number (because not all of our calls are not completely fixed due to requiring extra development etc.). It will link to a workflow management system that will alert the appropriate engineer as to the call being received and being assigned to him, providing a brief outline of the faults description.
Because we have multiple cusotmers, we have provided them with a single point of contact (each customer has multiple sites). As there are well over 25,000 assets per customer, the database will only show the assets that belong to a specific customer.
The database will also keep reports for each customer seperate so as to avoid confusing the customer representative when they perform the annual review.
This is just the basics of what i have been given to design and build (while trying to make it cheaper and more scaleable than pre-made software on the market).
As you could probably assume, i said just to go for one of the pre-made packages, but the company feels that this method would be cheaper in the long run due to not having to update licenses, incurr support costs etc.
I feel confident in saying you need to look at an enterprise level SQL server and application-of-your-choice for a front end. The fact that your customers can have over 25,000 records is enough by itself to warrant something a bit more flexible/powerful. If budget allows, you may want to seriously consider making the jump to MSSQL Server. I imagine you're going to run into some complex issues that will cause you great headache on any non-enterprise level platform.
Access makes a PHENOMINAL front end when slapped on top of an mssql server, I really think you should do some research into going that route. MSSQL can take care of almost all of the backend trickery all by itself. Access has plenty of facilities for front end design provided you're fairly well versed in vba.
I don't think this would be a very clean stand alone app...
we already have a box running SQL server 2k and windows 2000 advanced server. Because i don't know much about programming in the first place (have done a spreadsheet with some fairly complex vba before), which is why i want to stay with visual basic.
The management basically want something that can be developed in house especially for our systems, and they want it to do everything that a £5000 out of the box solution can do. (and as usual, they want it yesterday)
Im happy enough to give it a shot, but im not sure how im going to be able to make something that can compete with programs on that kind of level.
So you already have an MSSQL server? That IS your solution then IMO. The financial hurdle is the major obstacle for most people making the jump. If you want to do the front end programming in VB while retaining the functionality that you will need in order to compete with that level of software, MS Access .adp -> MSSQL is the way to go.
£5000 sounds pretty goddamn cheap for a full custom enterprise platform by the way. I don't think your management has a full understanding of the TOTAL cost of a third-party package. Such systems would easily run $50,000 over here, and our IT industry is in the shitter.
the £5000 was just for the software only. the company has spent somewhere in the region of £30,000 (something like $60,000) on their previous helpdesk solution, but it hasn't really been used correctly in the past 5 years (the old "contract jobs are a higher priority" excuse again).
Just a suggestion. You could buid the tables etc in Access, and a seperate front end in Access. Test it with a few records, then when it's working OK upsize the tables SQL Server and link Access to SQL Server - then worry about the security.
I've done that a couple of times, and I've been surprised at how easy it has been to upsize to SQL Server.
That way at a later date you could get VB7 and bolt it on to your back end, while people have a eworking database system to use.