Question Bulk dump from Barcode Scanner to Access (1 Viewer)

[Daryl88]

I am setting up a database to track shipments to customers. I would like to use a portable scanner to first scan the barcode of a customer invoice (contains customer invoice info) and then the barcodes (which represent lot or batch codes) that are associated with that invoice. At the end of the day I'd like to be able to dump into access and, retain the invoice to product lot/batch code info.

I am not interested in tracking products but rather their lot or batch numbers. In the event of a problem or potential recall situation with a particular lot/batch, I could instantly identify all customers who received that lot.

I've seen posts that describe how scan into fields etc. I'm already doing that but I now need the ability to be mobile and do a bulk dump at days end.

 

[RuralGuy]

Does your scanner have this ability? Does the scanner include software to capture this "dump" to disk?

 

[Daryl88]

I have not yet purchased the scanner for this application. We have a scanner now that is tied to a computer and it works fine for entering into the database a scan at a time. I need to go mobile with this so the whse. people can scan throughout the day. If you have any recommendations on a suitable scanner I'm all ears. What's important is that I'm able to retain that invoice number to lot code of products (on that invoice) relationship.
Any help would be greatly appreciated.

 

[RuralGuy]

Your question should really be directed toward th scanner you select. If their data dump includes the information you need and they have the software to capture it and create a file that is in a format that Access can read, then Access *should* be able to import it and maintain the relationship. There just isn't enough information yet to say with any certainty.

 

[Daryl88]

Hi, thanks for the response. It looks like most of these scanners can store and dump in .csv format. I'm fairly certain that Access can import that type of data. I'm just not sure if I would have to dump it, save it, import it into Access or if I could dump it directly into Access and keep the relationships intact?

 

[RuralGuy]

Yes Access can import a .csv file and if the data is in the order you scanned it then you can use VBA to put it together with the relationship intact.

 

[Fifty2One]

After actually trying the method you are suggestion I have upgraded to real time scanners. The biggest problem with storing data in the scanner is losing an entire days worth (or what ever the dump dwell period is) and having to either cope with the dataloss or having someone re-enter the data.
The other flaw in capturing a batch and periodically uploading the data is that if there is a potential problem with a batch or lot you have to wait until end of the dwell period to identify the customers or location of the spoiled batches - not instantaly.
There are RF scanners or hardwire one to a laptop and do wireless comunication from the laptop. I have found the second option is a bit more pricey but definately more reliable and multifunctional as now you can design what is basically a mobile desk with all the computer tools the end user needs.

I am setting up a database to track shipments to customers. I would like to use a portable scanner to first scan the barcode of a customer invoice (contains customer invoice info) and then the barcodes (which represent lot or batch codes) that are associated with that invoice. At the end of the day I'd like to be able to dump into access and, retain the invoice to product lot/batch code info.

I am not interested in tracking products but rather their lot or batch numbers. In the event of a problem or potential recall situation with a particular lot/batch, I could instantly identify all customers who received that lot.

I've seen posts that describe how scan into fields etc. I'm already doing that but I now need the ability to be mobile and do a bulk dump at days end.

 

[RuralGuy]

Nice post Fifty2One! Great 1st hand experience.

 

[Daryl88]

Thank you both RuralGuy & Fifty2One. Data loss would definetely be a major headache. Sounds like the laptop approach might make a lot of sense. I know some of these scanners are what they call portable computers with Windows CE or whatever it is. I think they include versions of Excel and maybe word but I haven't run across Access. Any thoughts?

 

[Fifty2One]

I personally perfer the laptop approach as it can be loaded with all the working tools of the end user including the client side of the SQL engine. This reduces the chance of data loss as you skip a transfer going from a laptop to the back end rather then scanner to PC to backend. Also the laptop will accept full versions of applications rather then so called optimized versions with some features which may be needed not included.
This way it is then very easy to make a front end app to collect and process the data in the field and then upload pertinent data to the network. If you want to use wireless you have better physical range available with a laptop then a wireless scanner. Otherwise you could just have the laptop connect to the network by hardwire and have the end user activate an upload feature within the app. Connectivity back to the network is also not an issue and the laptop can have other work tools installed. This also minimizes the need for additional training for the end users and also reduces the limit on sourcing replacement and service parts for your data gathering system. This will give you real time if you have wireless connectivity rather then bulk dump time.

Well I am not an expert, but just based on prior experience and working by usage and making the mistakes. I have previously used this approach for order filling with Access where the pick list is filled by a stock person, the serial numbers were scanned in and quantities keyed in for items not serialized. It also kept record of the box sequence number the items were placed in. The box sequence numbers were tied to the bill of lading for the outbound shipments so the customer would be able to find their widget upon arrival by retrieving the correct box out of the shipment. This was also helpful if an item was prepared for shipping and was recalled by the quality control area for any reason.
Same laptops were used for inventory spot checks, cycle counts, FIFO stocking and dock to stock controls for inbound material.

Thank you both RuralGuy & Fifty2One. Data loss would definetely be a major headache. Sounds like the laptop approach might make a lot of sense. I know some of these scanners are what they call portable computers with Windows CE or whatever it is. I think they include versions of Excel and maybe word but I haven't run across Access. Any thoughts?

 

[Daryl88]

OK you've convinced me. Now I have to convince my Shipping Mgr. Thanks again for your input!