Universal Access updates (Access97-SR2)

Topic

New Reply

My boss has come up with the brilliant idea of wanting every operator to have the ability to update the status of jobs from their desktop.
Problem 1 -Few have Access
Problem 2 – It’s a memo field
Problem 3 – It’s a mixed bag of PCs and MACs
Haves
The only common factor is that they all have Word and (Excel, which I use to track time), and a common network connection.

Have Nots:
We do not use Outlook, so sending out auto-reports based on employee doesn’t seem like an option.
Using the intranet for this data is also not an option with our autonomous ISD section.

The factors involved would be a Work Order # (WO)[There is also a WO_ID],
employee number (Emp) and a (status) field.

Here are their datatypes:
WO=Text (e.g. A1234
Emp=Long
Status= Memo ( This is the crucial one- but I’m seriously considering converting this back to text) It is not a deal breaker!

I’m thinking that even if I can grant them a common data portal, the real problem is confirming that the operator has the correct WO before updating.

I hope I’m being clear…
Does any of this sound familiar ?

Reply | Quote

Replies

I’m not clear on what Access has to do with it unless you plan to install the runtime on each machine, and you can’t do that on the MACs.

The memo field may very well be the deal breaker because they definitely *break* in Excel. You might be better off building a front end for them in VB, although I’m not sure where that leaves the MACS. To handle the mixed bag, you might be better off sticking with Excel and importing the results into a central Access database for analysis.

Reply | Quote

I had a feeling that would be confusing…Sorry.

We would like operators(PC and MAC) to be able to update the status of their projects in the dB records.
Naturally, I’m not going to give everyone access to the dB.
Even if I would, I couldn’t because most don’t have Access.
I want to compare a linked table consisting of a range in an Excel spreadsheet,[Table2] to the primary job table residing in the dB [Table1].
There is already an existing series of spreadsheets that link these operators together, so this seems like a logical extension.

Scenario: On Table 2(Excel-range), take the value of Column A(JobNO) and the value of Column E(Status), then compare it to JobNo and status in the dbTable[Table1]. I realize I may have to compare string values, Len would probably be a good boolean test, or if there was a way to time stamp Excel entries, that could also serve well as source of validation.

I hope that helps clarify…
I’ve seen what you guys do with mailclients, and API files.
This must be bush league in comparison… Some day, for me too.

Reply | Quote

It sounds to me that if you are mucking around with different platforms and spreadsheets (linked??) eeek, I would go for a nice ‘cross-platform’ option that could interact with your database and doesn’t need any software installe don the ‘client’ machines apart from a web browser ie. ASP.

Of course, this relies on the fact that you have an intranet (or at least the ability to set one up with IIS) and someone who could write the asps (or the time to do/learn it yourself).

This may be completely out of the question, but it is certainly what came to mind for me. I’ve done a couple of ‘sites’ / web applications now that interact with Access 2K databases just nicely.

jayden

Reply | Quote

Well, the ASP option is a sure fix, but I’m not sure that I could sell the pack in ISD into letting me have my own space.
(they really frown on anything not approved by the head cheesy poofs)
..then again, code compliance and optimization IS their job.

Problem is I don’t have Access2k, so all those groovy plug-ins aren’t available.
FrontPage?-Sorry, Netscape is the official sponsor of the 2001 lowest bidder awards,
so everyone that needs to use the system doesn’t have IE.
I’m not exactly thrilled with the way the browsers interpret different WYSIWYG generated HTML code either.
Javascript is a larger nightmare.
Data transfer seems like a very dim possibility under these circumstances.

I really appreciate your assistance though.

Reply | Quote

OK, as long as you can link the Excel sheets to the database, you should be able to do it with a query, but you’re going to have to handle the datatypes between the two tables somehow, especially if you try to join them on the JobNo. You’ll either have to have text on both sides of the join or use an implicit join instead of an actual one. There’s no way to reliably datestamp and Excel row that I know of, so you’re going to have to use the values within the records to match apples to apples.

That gives you a starting point, but you didn’t say what you wanted to do from there. Are you trying to see if the same JobNo has the same Status in both tables? If you only want to see those that don’t have the same status, then set criteria in your query to only return records where the JobNo is the same but the Status is different. The you can use that query as the basis for an update query to update your Access table from the spreadsheet.

Reply | Quote

OK, as long as you can link the Excel sheets to the database-

I don’t know if I can do that.
They didn’t install MS query in my unit- don’t I need that?

What are my options?

The data itself is already matched. (text)
It needed to be text to accomodate the wacky job numbering system my boss came up with.
Use a letter as a prefix to a 4 digit number
A#### – A = 2000 B= 2001 C = 2002 ( you get the idea)
I said what happens when we hit Z?
He replied that the system should be obsolete in 26 years anyway, or then it’s someone else’s problem.
I explained that this was the exact same type of thinking that caused the y2k problem…
Naturally, he was unaffected.

In the end, all I need to do is check that it’s the same job number and update the status column
if it needs it….setting that type of condition is the tricky part.

Reply | Quote

Why would you need MS Query? Access has its own query engine. The other Office apps use MS Query. You are doing this querying from Access, right?

Reply | Quote

Yes, I would be doing this from Access.

I never learned a query def, and I imagine that’s what’s going to go down.
I can import speadsheets, but querying from one is another ballgame

Doing all of this in ACC97 makes it tough to track down documentation.
Thanks

Reply | Quote

I’ve got a procedure that exports the data in (Table A) to Excel with all the fields I need
and then a linked table (Table which carries back to the dB.
Then a query that compares the two.

How can I define criteria in 1 field of Table B by string value,
then use the outcome to update the same field in table A?

I’m thinking a boolean test based on string length.
Sound reasonable..or am I oversimplifying it?

Reply | Quote