Replies

Linked tables can get a little nasty in this regard. Two options that I’ve used are (1) make local copies of the linked tables using one of the conversion functions to change data types, then base your query on the local table copies. You can look up conversion functions in Help, but the two I most often make use of are CStr to convert a numeric data type to a string, or one of the string-to-numeric conversion functions like CLng or CDbl. (2) Create the query based on the linked tables in the visual interface just as though everything were OK, then check out the SQL view and edit the JOIN clause. For example, you’d change

SELECT Table1.*, Table2.*
FROM Table1
LEFT JOIN Table2 ON Table1.Field = Table2.Field

to

SELECT Table1.*, Table2.*
FROM Table1
LEFT JOIN Table2 ON CStr(Table1.Field) = CStr(Table2.Field)

This is nice as you don’t have to make local copies of the tables, but once you alter the JOIN clause the visual interface doesn’t know how to represent the join graphically, so you’re stuck in the SQL view. Best of luck!

…and yet somebody else butts in!

Hi, Dave! I think I see what’s going on; you have to create a virtual folder to point at the directory C:vpasp. Choose Start -> Programs -> Administrative Tools and launch the IIS snap in. Expand your computer name, then Web Sites. Right-click on Default Web Site, and choose to create a new virtual directory. Follow the wizard being sure to make C:vpasp be the “Web Site Content Directory”, and make sure that Run Scripts permissions are set on the virtual directory.

Let’s assume the alias you gave the new virtual directory was ShaneIsCool . As has already been mentioned, you have to address your ASP page via HTTP thusly: http://localhost/ShaneIsCool/MyASPPage.asp

Best of luck!

See if this attachment helps at all; it’s in Access 2000 format. The bits of interest are the form, the lone query, and the single line of code in the AfterUpdate event of the State combo box. Cheers!

Addtionally, I believe each client may make more than one connection behind the scenes, effectively reducing the number of user licenses you have available. While watching the processes running on my SQL Server, I:

Opened an Access XP db with a linked table — no processes from my workstation
Ran a query on the linked table — 3 processes from my workstation ID, 2 sleeping, 1 runnable
Closed the query, left the Access XP db open — 1 process with a status of sleeping
Closed the Access XP db — no processes from my workstation

So, how many processes count against your 5 license limit at any given time will be highly dependant on exactly what’s going on at each client. Perhaps the easiest steps to take are to encourage your users to close the app when not in use, or dynamically link to the SQL Server tables only as necessary, and sever the link immediately after. Best of luck!

In addition to what Wendell has already said, individual SQL databases and their log files are allocated an amount of disk space. This amount can either be completely static, or can grow as needed. You can find these settings by firing up Enterprise Mananger and drilling down to the database in question. Right-click on it, choose Properties, and examine the Data Files and Transaction Log tabs looking at the space allocated for both, whether they’re set to grow automatically or are static, and if they’re set to grow automatically, how. Have fun!

There’s nothing fun about LDAP binding strings! Here is the little bit that I know: CN stands for container, OU for organizational unit, sprinkle liberally with DCs. The easiest way to construct your string is to go to a server/workstation that has the AD Users and Computers MMC snap-in installed, and begin expanding the structure.

I’ll use an example from my company. We have an OU named “Branches”. That OU contains several OUs, each named for a branch location. Within each nested branch OU is yet another OU named “Users”. That final Users OU contains, you guessed it, users. So, an example of the LDAP binding string I used in this exercise is: LDAP://OU=Users,OU=Billings,OU=Branches,DC=istate,DC=com. This will bind to the Users OU in the Billings OU in the Branches OU in our AD domain. It does so using my credentials as I’m the person logged into my PC and validated against the domain. You’ll note that I didn’t choose a particular server to address; LDAP employees some logic that I frankly don’t understand to choose an appropriate server with the AD info present.

Great, I’ve managed to bind to the Users OU…now what? This is where the Alias1 variable comes in. The function uses the value of Alias1 as a filter to select a single user in the OU to which I’ve bound, i.e. the searcher.Filter = “(mailNickname=” + alias1 + “)” line in the code. You can find the mailNickName of a user by double-clicking their user account in the AD snap-in and looking at the “Exchange General” tab; or it’s probably the user portion of the email address everybody uses to send that person email.

I hope this helps a bit. I’ve found LDAP/ADSI/Active Directory manipulation to be an exercise in frustration, but also valuable functionality to have. I’m still on the steep side of the learning curve, though. Best of luck!

Not exactly the best annoted example ever, eh?!

The example is meant to be run from a command line, hence the function looking for arguments passed in, i.e. the args = Environment.GetGommandLineArgs() bit. Rather silly, I think. I changed it every so slightly and have attached the code in a text file. Here’s how to make it work:

Open VS.Net, and choose File -> New -> Project. Make it a VB Project and from the Templates pane, choose a Console Application; give it a name and a directory and click OK. Paste in the code from the attached text file — you like those nice blue squiggle lines?! We need to add a reference to the System.DirectoryServices namespace. BTW, you’ll note in the original sample from M$ that they didn’t fully qualify the SearchResultCollection or SearchResult classes.

Hard code in the alias on line 5 and the LDAP binding path on line 7. If you haven’t assembled a LDAP binding path before, or are unfamiliar with the structure of the Active Directory for your domain, consult your network admin or responsible tech staff. I commented out all of the properties the sample was trying to fetch and display except for Department. Why? ‘Cause if the value is not present for that user, you receive an error. Sure, I could’ve added error handling for that eventuality, but I was having a hard enough time making it work as is!

OK, almost home. Now build your solution and hit F5. Watch closely as a DOS window will jump up quickly, display the “Department” property for the user, and quickly close. Or, pull up a DOS window a la Start -> Run, type “CMD”, and call the executable that you’ve just built from that DOS window. Behold the wonder of .NET!

A couple of options are to (1) create a domain group with proper permissions to update the AD, and assign all users membership in that group (2) create a fictional single user with proper permissions to update the AD, and pass that fictional user’s creditials as you use LDAP.

The problem with 2, of course, is that the user credentials are hard coded and a potential security risk. The problem with 1 is that it may be more expansive than your network admin is comfortable with, and we all want happy network admins! The trade off here is less administrative work for tech support staff vs. a potential loss of some security.

What if, and don’t all cockamamie ideas start like that?!, you had an Exchange form with text boxes corresponding to the fields you want users to be able to update. The user fills out the form, and routing takes it to a tech with proper permissions to update the AD. The tech reviews the request, presses a button, and the AD is automagically updated with the data in the Exchange form using the tech’s credentials. This might be a happy medium where the user can kind of set their own info, a level of security and control over what’s in the AD is maintained, and tech time is kept to a minimum.

At any rate, I’d encourage you to sit down with your network admin, explain what you’re trying to do, and explore options together. Best of luck!

In design view for your delete query, right-click in the gray area in the top pane where your table(s) is displayed, and choose Properties. In the Query Properties box, check the value set for “Unique Records”. If it’s set to “No”, try changing it to “Yes” and run the query.

Doh! I may have typed too soon. The function I referenced was authored by Susan Sales Harkin in the November 2000 issue of Inside MS Access, and, as such, is copyrighted. Her article details how to use an ADO schema recordset to fetch a list of users with a database open. My apologies! You might contact the nice folks at Element K Journals to ask about a single back issue purchase. Personally, I find the function invaluable.

On the CD for Volume 1 of Access 2000 Developer’s Handbook by Getz, Litwin and Gilbert, there is a code module named CommonDlg that allows you to do just the thing you’re looking to do via the Windows File -> Open dialog box. As it is copyrighted material, and rightly so, I hesitate to post their code, but will post code that I wrote to use their module, and commend the book to you. In this function, I invoke Getz, et al’s module, pass it some parameters to configure the dialog box to my diabolical specifications, and import the chosen text file using an established import specification. Best of luck!

Public Function ImportFile()

Dim cdl As CommonDlg
Dim intPos As Integer
Dim strImport As String
'instantiate the CommonDlg class module
Set cdl = New CommonDlg

On Error GoTo HandleErrors
'set the options for the Open common dialog
With cdl
    'The types of file to display
    .Filter = "Text files (*.txt)" & "*.txt"
    'The directory in which to begin
    .InitDir = "C:Program FilesProcomm PlusDownload"
    'You can seed the surfing with a file name if you choose
    .FileName = "src1.txt"
    'Hide files marked as Read Only
    .OpenFlags = cdlOFNHideReadOnly
    'If the user clicks cancel, raise a trappable error
    .CancelError = True
 End With
'launch Open common dialog
cdl.ShowOpen
'Use selected file as source for import routine
strImport = cdl.FileName
DoCmd.TransferText acImportFixed, "Src1 Link Specification",  _
    "src1_txt", strImport, False, ""

ExitHere:
    Set cdl = Nothing
    Exit Function
    
HandleErrors:
    Select Case Err.Number
        Case cdlcancel
            MsgBox "You cancelled, doughhead"
            Resume ExitHere
        Case Else
            MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
    End Select
    
End Function

It sounds as though your problem is solved. There is another possibility whereby you would examine the .ldb file for that database to determine the number of users who have it open, in code, and not allow another instance to be opened if the number is above a threshold, in your case, one. This would eliminate relying on users to always use the shortcut you provide. If it’s of interest to you, post back and I’ll dig up the code.

Mark:

Give this a shot: <DateAdd("d",10,[Forms]![frmReportForm].[txtEndDate].[Value])

Make a new query, and make it a Totals query. Group by Project ID and employee ID, or employee name if necessary, and choose to Sum the hours worked field. Make that new query be the record source for your subform — walla! Good luck!

Can you post the SQL that comprises the query? Somebody may be able to find something…