Access help for the not quite idiotic

[spectrolab]

I hope someone can point me in the right direction. I have a reasonable grasp of the basics of access and would like to learn more. I have set up a laboratory information management system and it works ok, but I am looking at learning how to make it easier for the end user to work with and also how to use vba. Are there any resources out there that describe using access for this type of application or an example database to point me in the right direction. I would be willing to pay for assistance if required.

Thanks very much for your help!

 

[Pat Hartman]

Finding example databases for a specific line of business is unlikely. There are lots of example databases to be found. One comes with Access. Microsoft also has several excellent example databases available on their download site. You'll also find excellent samples here.

 

[spectrolab]

Thanks for that, I will have a look and see what I can find

 

[WayneRyan]

Spectrolab,

http://www.healthcarefreeware.com/lim.htm

hth,
Wayne

 

[JoeCruse]

Spectrolab,

if you do a search on importing text files, and focus on my username, you'll find a post that deals with writing some vba to import spectrometer data from a WD-XRF spectrometer's text file reporting setup. In fact, Wayne Ryan was a huge help with this (thanks again, Wayne ).

We use an Access based-"hand rolled" LIMS in our lab, that I started building in 97 and am always doing something with. This forum and a couple others have really helped me with our LIMS in the last several months, and have especially helped me to better normalize my original LIMS tables and structures.

As Pat noted, it's unlikely to find specific examples. I still have not come across many, at least not free ones. There are some here (and other sites) which have features I either already have in ours, or want in ours. I like to download them and look at how someone else does it, in order to build my education and help make our LIMS better (good example is lookup fields. I implemented these in our LIMS because MS used them in the demo Northwind app, but have found from this site that it is not wise practice, and am in process of updating my LIMS). Even then, you run into issues that possibly only you might face, and no examples anywhere (hence my post for help on importing).

By the way, is it wrong to assume your lab has SPECTRO instruments, given your username??? We run a Ciros ICP, though it's not our main instrument. Getting that data into the LIMS is a real PAIN, especially given that the only way to get data is to open the file as a delimited file in Excel and then cut and paste what you need.

Good luck to you.

 

[spectrolab]

Hi Joe,

Thanks for the info, it was very informative. We also use SuperQ (pretty much the only software) and import our data in a different way. I know that the latest versions (on the 14xx series machines) allows you to auto export results files and call a dos based application to do the data manipulation, importing etc, but I haven't be able to get anything to work. What we do is select the results in batches (usually more than one result) and print them as a delimited text file saved with a specific name (i.e blastholes.txt) Then from access I call a excel macro to format the data (remove all the stuff we don't need, like dates and sequnce numbers) and save the file as an excel spreadsheet. Another code module then imports the data to an access table.

This isn't exactly elegant, but it works for us. Can i assume from your code that the name of your text file doesn't change?

Have you tried anything to get data to automatically go into the LIMS? I have seen this work on CCLAS lims, but I haven't got a copy of the file, unfortunately. And I can't afford $30K for CCLAS, not many small labs can.

It's good to talk to a fellow Panalytical user, are you in Australia by any chance?

I will see if we can adapt something from the code, thanks Joe and Wayne!

 

[JoeCruse]

Lims

Hey Spectrolab!

A fellow PANalytical user, cool! I am based in the US, in the state of Kentucky, and I run a lab for an alloy manufacturer. We've been PANalytical customers since the 1960's, I believe, though I heard they had an ARL in here in the mid 1960's as well. I still have a couple of the circuit boards from our old PW-1212 .

I started developing a LIMS when we bought a PW2400, running SuperQ 1.0. We noticed it was using a .MDB file to store everything, instead of the flat files of X40. We were able to link the Results and Result Concentration tables to our LIMS and run an append query to get the instrument's analytical data for our use (this REALLY freaked Phillips people out, because they didn't think you could do that. In fact, I don't think they were happy about it). That ability changed after the 3.0 upgrades, when they pulled all analytical data out of SuperQ.MDB and made .RDB files for all archives used. I contacted PANalytical for help, and got nowhere. I also posted on Mike Cheatam's excellent XRF-List server, but no one was doing this with a LIMS, so I was stuck. I knew SuperQ would transmit results files to about anywhere you wish, so I looked at the formats offered (4 I think), and settled on the one that suited us, and then went looking for help at this site in pulling stuff from text files into the LIMS. I had to resort to some trickery for this, as SuperQ results files come as .QAN files. This extension is not recognized as a text file by WIN XP, and XP won't let you add this to the list of accepted text files. I wrote my code to tell the LIMS to go look at the destination folder for SuperQ text files every 5 seconds. When it finds one, it is to rename it to "SuperQ.txt". It then runs the text import procedure, which Wayne helped me write. It then deletes the text file. It will hang up if more than one text file shows up in the folder. But we ony see problems with this if there are network problems, or if the analysts forget to leave the LIMS running when SuperQ is running (24/7).

It's not perfect, but it actually is a bit better than what I had before, as this is automated, and our old setup forced the analysts to click a button to import data.

Great to talk to another PANalytical user, especially from another country.

Good luck. This is the best Access site I've run across, the people here make it great.

 

[WayneRyan]

Spectro & Joe,

Looks like you two are getting along just fine. Joe, glad to hear that things
are working out well for you. Hopefully, Spectro will enjoy similar success.

see ya,
Wayne