Replies

Forgive me Father, for I am a relative newcomer as well; be sure you take my advice with a healthy dose of salt!

On the server, check to see if a copy of the Microsoft Data Access Components (MDAC) has been loaded; you can quickly check by surfing to the path you specify in the File attribute of your METADATA tag and looking for the ADO .dll you specify. If it hasn’t been loaded, perhaps you can work with the staff at the ISP to see if it is possible. The latest version of the MDAC is available for free download from our friends in Redmond.

First things first, I guess. Can you or someone at the ISP make sure that the ADO library is loaded on the server?

Check out the Partition function.

Thanks, Charlotte! I think your ideas have great merit, and that’s probably the route I’ll go.

Here’s the scoop. My company has fewer parking spaces than employees, and we must conduct a random pick for the lucky few that get to park close each week. The flow that I have at present is this:

A table with employee’s names and ID#.
A make table query that uses the Rnd() function against the employee’s ID and top values to get a list of winners,(tbl_Winners).
A bit of code that adds a autonumber field to tbl_Winners.
A select query that joins tbl_Winners with a list of parking space #’s, joined autonumber to Parking#.

The form is based on that last select query. As I mentioned before, on that form I’d like to have a “Get new winners” button that launches the flow above and the form refreshes. The problem, of course, is that tbl_Winners is locked by the select query which is the record source of the form.

So, why the make table query in the first place? For HR purposes, they need a static data set they can refer to for present winners (as you might expect, people are pretty grumpy about the prospect of not being able to park close), but also because I need a table that I can add an autonumber field to so I can easily assign a parking spot.

Does this clarify or muddy the idea of what I’m trying to do? As we’re talking about a very small db, I could also post it…

One other quick thought: it is my understanding that queries are saved in an optimized state, recordsets are not. So, in theory, a contest between a saved query and a recordset that performs exactly the same function but is created and executed in code will see the saved query emerge victorious in terms of time to execute.

Charlotte, folks at large, would you agree?

Let’s try this: first things first, back up your database.

With that done, I’ll operate under the assumption that each client has an ID of some sort that uniquely identifies them. Let’s simplify things by saying that there are only 3 fields of information you originally received, ID, Name and City, and since then, you’ve added a field of your own called Rank. In a query, you can link your original table with the new table based on the unique ID field. At this point, you can change the query to be an Update action query, and choose to update your original table with data from the new table, but you get to control what fields of information are updated. Make sense so far?

A quick caveat here: if you have any trailing spaces in your names, a la

"DaveSchmaveX  "

, taking the right-most 1 characters will get you the space. You may want to use the Right() function in conjunction with the Trim() function which will trim off all preceding and trailing spaces:

Right(Trim([FieldName]),1)

Post it, and they will come.

I found Beginning Access 2000 VBA from Wrox Press to be a well-written book for learning VBA with a specific eye towards Access. I’ve had two employees make their way through the book to great effect.

That said, I’m of the opinion that nothing beats a good instructor led course; see if you can get your employer to pony up the cash!

Roberta:

There sure is; check out the Rnd( ) function as well as the Randomize statement in Access help. If you have questions, post back.

A note of caution: I started to develop an app for my company to use for conducting random drug tests of its commercial drivers. After chatting with the folks in HR, we decided to use a 3d party product to minimize our legal liability. I’m not sure that this particularly applies to you, but…

Be sure that you have a reference set to the DAO object library; by default, Access 2000 will only include a reference to ADO.

David:

The web server needs to be told who the user is, either by the traditional “Enter user name and password” form, or possibly by using integrated Windows security if it’s being accessed on an intranet. Once the server knows who the person is, you can write a cookie to the user’s PC which the server can access again at a later time. Of course, if the user clears their cookies, it’s back to square 1!

I was actually looking for a way to get the equivalent of an API call to get the login name of the user accessing my ASP page; I should have been more specific in my original post, and it was only after spending a week with the nice folks at AppDev that I was able to figure it out.

If you’re developing on an internal, NT based web, and the virtual directory of your ASP page is set to not allow anonymous access but to use integrated Windows authentication, you can use Request.ServerVariables(“AUTH_USER”) to get the login name. The function I’m using is below; let me know if anyone has a more elegant way to go about it.
””””””””””’

Here is the person logged into the network:

The above derived from the below – Request.ServerVariables(“AUTH_USER”):

I’m sorry and a touch embarassed to say that I’m not exactly sure in a relational sense why that’s a necessary step. That said, I’ve seen it only be a necessary step when the delete query involves a join between multiple tables.

Anybody else able to better illuminate the situation and give both of us a schooling in relational theory?