Barcode Scan Into SQL Server

Question

Barcode Scan Into SQL Server

JP789

SSC Enthusiast

Points: 197
More actions
October 19, 2021 at 1:55 am

#3941803

We have an Access front end with a SQL Server backend. We have inventory that we need to scan and keep track of. Is there a way to scan barcodes into a SQL Server table? I'm not sure if it would be better to use a tablet or phone scan or something else. We are looking to scan the inventory to the backend SQL table and then be able to view it in MS Access front end and track inventory. Any ideas on the best way to do this is appreciated.

Thanks,
JP
March 7, 2023 at 12:01 pm

This was removed by the editor as SPAM
August 31, 2023 at 6:30 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

JP789 wrote:

We have an Access front end with a SQL Server backend. We have inventory that we need to scan and keep track of. Is there a way to scan barcodes into a SQL Server table? I'm not sure if it would be better to use a tablet or phone scan or something else. We are looking to scan the inventory to the backend SQL table and then be able to view it in MS Access front end and track inventory. Any ideas on the best way to do this is appreciated.
Thanks,
JP

There are two basic types of scanners: Those that scan and store into their own memory and those that scan and store to some memory over wifi or physical connection.

In both cases, they store to some form of repository. That repository can be a file and that file can be in CSV, TSV, Fixed Width, JSON, or XML files. Those are pretty easy to import and parse, sometimes doing both at the same time. Some scanners come with their own programs and some form of a database. That database might be directly interrogated by SQL or there might be a CLI (Command Line Interface) that can be tickled to export directly into an SQL Server staging table, etc, etc.

The key to your issues will be to ask the right questions when researching which scanner system to buy. You have to know where the data is originally stored and where it needs to be for it to either be pushed into SQL Server or pulled into SQL Server.

And, no... I've not had to work with any scanner systems in about 2 decades, so I have to recommendations as to which scanner might suite your needs by either function nor price.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 2

The choice of device has many factors to consider, location where it is to be used would be my first concern. Using a device in a nice clean office where it is treated well is completely different to a device for a warehouse which would be dirty, dusty and would be treated harshly. Also consider the operating system, the software you are going to use, the type of barcode (1D, 2D, 3D) and symbology, you specified inventory which could be EAN, UPC or propriety. I have programmed many scanners, the latest being Android and they normally have a wedge app that can scan a code and paste into another app or if you write your own apps like I do then you can access the scanner direct and store the result directly into SQL Server.

Using Jeff's mantra "It Depends".

Far away is close at hand in the images of elsewhere.
Anon.

JP789 SSC Enthusiast Points: 197 More actions · Answer 3

Thank you for your advice! Yes, the scanner will be used in the warehouse. I will keep that in mine.

Thanks,

JP

JP789 SSC Enthusiast Points: 197 More actions · Answer 4

Thank you for your advice. While shopping for the device I'll keep the storage in mind.

Thanks,

JP

JP789 SSC Enthusiast Points: 197 More actions · Answer 5

You said: I have programmed many scanners, the latest being Android and they normally have a wedge app that can scan a code and paste into another app or if you write your own apps like I do then you can access the scanner direct and store the result directly into SQL Server.

Good Idea! I'll look into it.

Thank you,

JP

Zidar SSC Enthusiast Points: 126 More actions · Answer 6

barcode scanner is a simple data entry device. It scans over barcode lines (bars, hence bar-code) and translate bars to text. Some by default add <carriage return> character at the end. On others, you have to 'program' the scanner so it adds RETURN/ENTER at the end.

Open your table, position cursor at the beginning of desired field and point scanner to the barcode. The text appears in the filed. If the scanner adds RETURN/ENTER, your cursor is where it is supposed to go next. For mass scanning, usually barcodes are scanned into some spreadsheet-like structure. So yes, you can scan it into SQL table directly. However, scanning directly into SQL tables is not a good idea.

I have many years of experience with barcodes, and would recommend and MS Access front end, using datasheet form which has its source in linked SQL table. Mind you, things are never as simple as they look like. Table that will receive scanned barcodes should be designed properly - with timestamping each row, perhaps adding operators ID, so you at least know who did what. Also, column for barcodes in the table should be unique. No point scanning same item multiple times. At Point Of Sale they scan same item multiple times, 3 times for 3 soap bars, but in warehouse environment it is not so. You may need a mechanism (program code in Access) to handle possible duplicate entries. Surprisingly, barcode scanners are not perfect. They do make misreads, each scanner is different, but the error in scanning rate could be as high as 1 in 1000. Normally it is better, but do not expect less than 1 error in 10,000 scans. This requires some mechanism for validating scanned barcodes. If you are scanning automobile parts, you do not want some prankster to scan bag of chips, eh? Maybe a kind of lookup table is in order, that contains all possible valid barcodes?

You need to know what type of barcode is used, and calibrate each scanner properly. Then, perhaps the most important thing is knowing the process that scanning barcodes is supporting. If process requires same barcoded item to be scanned at different stations, then design of the table where barcodes are stored becomes more complex - barcode is not unique, it must be accompanied with something else (no time stamp!)

If you need to produce, draw barcodes yourself, it is yet different story. Sometimes you can get away with using Windows fonts of barcode type, sometimes you must generate them yourself, print on stickers then apply stickers to the items, which again may be constrained by the process you are supporting.

Many things to do before you start scanning. Good luck and ask more if you need to.

🙂

Zidar's Theorem: The best code is no code at all...