Access Unique ID order and attachment questions

[mrenschl]

I am a MS 2000 Professional user and I have come upon a problem with unique identifier order in an Access file I've created. The purpose of the database is to have a record of what programs WTIU has produced over the past 35 years. In other words, it is a simple media management database. The problem with the order of unique identifiers is that they end up in the wrong order and not in ascending order like I would like to have. For example, a few IDs we have are as follows:

AR-1
AR-2
AR-3
AR-4
AR-5
AR-6
AR-7
AR-8
AR-9
AR-10
AR-11

When you get back into the database, the order becomes
AR-1, AR-10, AR-11, AR-2 and so forth. Is there a way to get ascending order like the first list in this thread?

My other question involves sending an Access file as an attachment via e-mail. I have tried to send the database to a colleague and this is the message I get concerning the attachment:

Your message sent <To: > was delivered without the attached WTIU Programs.mdb file. Because a virus or malicious scripts can be easily distributed in executables, scripts, and compressed files, many file attachments are being removed before delivery to protect our users.

I know I can export the file to an Excel Workbook, but this colleague needs to look at the structure of our database instead of seeing the fields and information as they appear in the Excel file. Is there a way to send the Access file without compromising the structure of the database? Any help on these subjects is much appreciated. Thank you.

 

[BNPMike]

Access only has default domains like numbers and alphanumeric. The way I get around this is to maintain a separate (hidden) field like AR-001, AR-002 etc You can load this using string functions but you'd be better changing the formatting rules of the identifiers so that they sort naturally.

 

[Amiel]

I do not have Access on this machine in oder to test this response for you. You can do this easily yourself however.

Use a SQL query with the 'Mid' function to extract the characters to the right of the three alpha characters(ie. "AR-") what remains can be cast to numeric using he function "CLong" which is then ordered by it's (numeric) value.

Looks something like this:

"Select CLong( Mid([fieldinquestion], 4, 10) ) as myNewKeyval from [tableinquestion] order by myNewKeyval"

The value 10 used can be replaced with the field length
or any realistic number representing the maximum length that will ever be encountered in the database field.

The real solution would have been to use autonumber fields in the table in the initial design.

Eventually and for simplicities sake perhaps you can create a new table identical to the existing one except the added autonumber field and insert your existing records using a query like the one above.

These kinds of problems are not uncommon by the way.

Good luck.



Amiel
amielzz@netscape.net

 

[Amiel]

The second part of your question describes the response that you recieved from some e-mail filtering software that prohibits the inclusion of binary or mixed binary files as attachments.

No reason to attempt to circumvent this (security) to sent information on your database structure.

Access has a feature that allows you to print all of the details of your database's structure(s).

Again I don't have a copy of Access on this machine so I can't describe the specific keystrokes to reach it. It is in the 'Database Utilities' dropdown somewhere however.

This should do the trick for you.

Amiel
amielzz@netscape.net