Question

Locked

How to write a simple access query

By davies_m ·
I'd be grateful if anyone could help me with the following:

What I'm trying to do is write a simple access query to change the format of the logon name from Username_Initial to firstname.surname. I have 3 separate fields one holding the Firstname, one holding the surname and the other holding the old Logon information.

any help would be appreciated.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Re simple access query.....Will this help you with your issue?

Access query to change the format of the logon name from Username.

There are three main steps to use IDC files to query a secure Microsoft Access database:

Use ODBC Administrator to create a System DSN that points to the workgroup information file (System.mdw) you use with your secured database.

Create an HTML form that requests a username and password. The HTML form passes the values to parameters in your IDC file.

Modify the IDC file to use the username and password parameters to authenticate user access to your database.
You must use an HTML form to enter the username and password, and then pass that information to the IDC files. You cannot configure Microsoft Internet Information Server (IIS) Basic Authentication or NT Challenge/Response to achieve this functionality because those IIS options authenticate users against Microsoft Windows NT permissions, not Microsoft Access security accounts. It is possible to use Basic Authentication and NT Challenge/Response with Microsoft SQL Server databases because SQL Server can be integrated with NT Security. Microsoft Access security does not provide that capability.

This example contains the following sections:

Creating a Secure Copy of Northwind.mdb

Creating a System DSN for a Secure Microsoft Access Database

Creating the HTX/IDC Files and the HTML Logon Form

Customizing the IDC Files

Testing the Query

Creating a Secure Copy of Northwind.mdb

1.
Copy the sample database Northwind.mdb to C:\My Documents\Northwind2.mdb.
2.
Start the Workgroup Administrator by executing the Wrkgadm.exe file. The file is installed to your Windows\System folder by default. You can find a shortcut to this file in the C:\Program Files\Microsoft Office folder.
3.
In the Workgroup Administrator dialog box, click Create.
4.
In the Workgroup Owner Information dialog box type the following information, and then click OK:
Name: <Your name>
Organization: Northwind Traders
Workgroup I 1234

5.
In the Workgroup Information File dialog box, make a note of the path and file name that appears in the Database box so you can rejoin that workgroup file when you are finished. Then type C:\My Documents\Northwind2.mdw, and click OK.
6.
In the Confirm Workgroup Information dialog box, verify that the information is correct, and then click OK.
7.
Click OK when you see the message that you have successfully created the workgroup file, and then click Exit in the Workgroup Administrator dialog box.
8.
Start Microsoft Access and open C:\My Documents\Northwind2.mdb.
9.
On the Tools menu, point to Security, and then click User And Group Accounts.
10.
Click the Change Logon Password tab, and type Admin in the New Password and Verify boxes. Click OK.
11.
Quit Microsoft Access.
12.
Copy the Northwind2.mdb and Northwind2.mdw files to a folder on your Web Server computer, or to a network location that you can access from your Web Server computer. Be sure to retain a copy of Northwind2.mdb on your local drive for use later in this example.

At this point, you can rejoin your original workgroup information file.
13.
Start the Workgroup Administrator program following the procedure in step 2 of this section.
14.
Click the Join button.
15.
In the Workgroup Information File dialog box, type the path and file name of the system database that you noted in step 5, and then click OK.
16.
Click OK when you receive the message that you have successfully joined the workgroup, and then click Exit in the Workgroup Administrator dialog box.

Creating a System DSN for a Secure Microsoft Access Database

1.
Double-click the ODBC icon in Control Panel on your Web Server.
2.
In the ODBC Data Source Administrator dialog box, click the System DSN tab.
3.
Click the Add button.
4.
Select Microsoft Access Driver, and then click Finish.

NOTE: If the Microsoft Access Driver does not appear, it is not installed on your Web server. For information about installing the driver on your Web server, search the Help Index for "Microsoft Access Desktop driver," or ask the Microsoft Access 97 Office Assistant.
5.
In the ODBC Microsoft Access 97 Setup dialog box, type NorthwindIDC in the Data Source Name box.
6.
Click the Select button and browse to select Northwind2.mdb. Click OK.
7.
In the System Database box, click Database, and then click the System Database button. Browse to select Northwind2.mdw, and then click OK.
8.
Note that you have the option to click the Advanced button in the ODBC Microsoft Access 97 Setup dialog box, and set a default Login name and Password for the System DSN. Any of your IDC files that do not provide a username and password will use the default settings.
9.
Click OK to close the ODBC Microsoft Access 97 Setup dialog box.
10.
Click OK to close the ODBC Data Source Administrator dialog box.

Creating the HTX/IDC Files and the HTML Logon Form
In this section, you create a query with username and password parameters, and then export the query to HTX/IDC format. When you create IDC files from a parameter query, Microsoft Access automatically creates an HTML form for entering the parameters. This is an easy way to create the HTML form you need to collect the username and password information. However, you do not have to use this technique to create the HTML form; you can use Notepad or another tool, such as Microsoft Front Page 97, to create your own HTML Logon form.
1.
Start Microsoft Access.
2.
Open Northwind2.mdb.
3.
Create a new query called SecureIDC based on the Customers table:
Query: SecureIDC
----------------
Type: Select Query

Field: CustomerID
Table: Customers

4.
On the Query menu, click Parameters.
5.
Type the following in the Query Parameters dialog box, and then click OK.
Parameter Data Type
----------------------------
[UserParam] Text
[PassParam] Text

6.
Save the SecureIDC query and close it.
7.
Select the SecureIDC query in the Database window, and then click Save As/Export on the File menu.
8.
In the Save As dialog box, click "To an External File or Database," and then click OK.
9.
In the "Save Query 'SecureIDC' In" dialog box, select Microsoft IIS 1-2 (*.htx;*.idc) in the Save As Type box, and type SecureIDC.htx in the File Name box. Click Export.
10.
In the HTX/IDC Output Options dialog box, type NorthwindIDC in the Data Source Name box, and then click OK.
11.
Click OK in each of the two Enter Parameter Value dialog boxes that appear.
12.
The HTX/IDC output creates three files: SecureIDC.HTML, SecureIDC.htx and SecureIDC.IDC

Customizing the IDC Files
NOTE: This section contains information about editing IDC and HTML files, and assumes that you are familiar with editing HTML files. Microsoft product support professionals do not support customization of any HTML, HTX, IDC, or ASP files.
1.
Use Notepad or another text editor to open the SecureIDC.IDC file. The Password and Username fields show nothing entered next to them by default. You must add the parameters that will hold the values from the HTML Logon form. Change the SecureIDC.IDC file so it looks as follows:
Datasource: NorthwindIDC
Template:SecureIDC.htx
Username:%[UserParam]%
Password:%[PassParam]%
SQLStatement:SELECT Customers.CustomerID
+FROM Customers;

2.
Save the SecureIDC.IDC file and close it.
3.
Use Notepad or another text editor to open the SecureIDC.HTML file. By default, the HTML form uses the GET method to submit its data. GET variables appear in the address box of Web browsers. Therefore, you must change the GET method to the POST method if you do not want your password to be visible in the address box of your Web browser. Locate the following line in the SecureIDC.HTML file
<FORM METHOD="GET" ACTION="SecureIDC.IDC">

and change it to:
<FORM METHOD="POST" ACTION="SecureIDC.IDC">

4.
Text boxes use an Input Type setting of Text by default. In order to prevent your password from being visible in the text box on your form, you must change the Input Type to Password. Locate the following line in the SecureIDC.HTML file
[PassParam] <INPUT TYPE="Text" NAME="[PassParam]"><P>

and change it to:
[PassParam] <INPUT TYPE="Password" NAME="[PassParam]"><P>

5.
Save the SecureIDC.HTML file and close it.
6.
Copy SecureIDC.HTML, SecureIDC.htx and SecureIDC.IDC to a folder on your Web Server computer where you have both Read and Execute permission.
For more information about configuring Microsoft Internet Information Server (IIS) permissions, please refer to the IIS Help Index, and see the following article in the Microsoft Knowledge Base:

160754 (http://support.microsoft.com/kb/160754/EN-US/) ACC97: Error "HTTP/1.0 403 Access Forbidden" Browsing IDC Page
Testing the Query

1.
Start Microsoft Internet Explorer 3.0, or another Web browser program.
2.
Type the Uniform Resource Locator (URL) in the address box of your Web browser to view SecureIDC.HTML. For example, if you saved your IDC files in a folder called Test in the wwwroot folder of your Web Server, type:
http://<servername>/test/SecureIDC.HTML (http://<servername>/test/SecureIDC.HTML)

Note that the URL depends upon where your files are located on the Web Server.
3.
The SecureIDC.HTML form opens in your Web browser with a [UserParam] box, a [PassParam] box, and a Run Query button. Type Admin in both boxes, and then click the Run Query button. The SecureIDC.IDC file opens and displays a list of CustomerIDs.

NOTE: If you type an incorrect username or password, you receive the following error:
Error Performing Query
Not a valid account name or password.

Please post back if you have any more problems or questions.
If this info is useful, please give a thumbs up. Thanks

Collapse -

what ???

by Gate keeper In reply to How to write a simple acc ...

I havent used access in a long time, but vba was a component of ms access which does have string manipulation functions such as left,right,mid,len etc.

I'm not sure it can be don within a query .. or are you trying to change the data itself or how it is displayed ?
are you using another front-end ?

Collapse -

Well I'm not sure I get you, but

by Tony Hopkinson In reply to How to write a simple acc ...

Update [Logons]
Set [LogOnName] = [FirstName] + '.' + [LastName]

would do it, maybe.

P.S. waht are you going to do if you get two John Smiths ?

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums