Extract Smart card reader data to MS Access table (1 Viewer)

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
Hi All,

I have got an MS Access database works in a multi-user environment, I would like to extract data from the Smart card reader device and then insert it to my [Customers] table.
The card reader provides an XML file called: eRevealerGcc when you insert the card into it located in the %temp% folder in this path: C:\Users\nadia\AppData\Local\Temp\eRevealerGcc.xml.
As you see from the above path it specifies my user only (This must be put into consideration).
The XML file created only once you put your card then once you remove it will disappear, I do not know if it's possible to link my access database in this event to insert the data to my customers' table?

Please find attached the XML file.



Thank you so much in advance.
 

Attachments

  • eRevealerGcc.txt
    13.4 KB · Views: 29

theDBguy

I’m here to help
Staff member
Local time
Today, 15:05
Joined
Oct 29, 2018
Messages
13,130
Hi. Not in front of a computer now, but you should be able to link to an xml file or at least read it in as a text file using either the Open statement or FSO.
Sent from phone...
 

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
Thanks for your prompt response.
I missed you theDBguy, you are always on time, extremely appreciated that.
I would like to automatically get this data like by pressing a button through a data entry customer"s form to insert the xml attribute values to my form fields, in another word the Open statement as you stated. And put into consideration the multi user reference path.
In addition, in the later stage I will use your API pdf template to populate some fields from the customer form.

Thanks a lot, awaiting your always help.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 15:05
Joined
Oct 29, 2018
Messages
13,130
Hi. I'll see what I can do when I get back in front of a computer.
Sent from phone...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:05
Joined
Oct 29, 2018
Messages
13,130
Hi. Just a thought... Have you tried importing the xml data using the Ribbon?
 

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
Hello, sorry I was sleeping.
Yes, I tried but I got a fiew data of the first xml attributes without the rest, not as expected.
As I see it needs an expert like you, I don't think I can do it.
By the way I tried to import it to an Excel file I got the whole data but not as columns it became as rows, even though I want it to be MS Access.

Thanks.
 
Last edited:

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 17:05
Joined
Feb 28, 2001
Messages
18,311
SmartCard readers are commonly used in the military or in government agencies here in the USA, but not nearly as often in private industry. Usually when we had SmartCard activities with the Navy, we had a third-party program that would integrate with other programs (like Access) via a library file or .DLL file that would allow us to just read the results. If you are trying to read the raw results, I have no experience there because I always used the support programs. Sorry but I don't recall details enough to tell you much about them.

When I did a web search for "smart card reader programs" I got several hits including some for which the reader portion was free. You might wish to repeat that search yourself to see if anything useful comes up.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:05
Joined
Oct 29, 2018
Messages
13,130
Hi. When I tried to manually import your xml file into Access, I got three tables and all text data were there. The only things that didn't import are the photo and signature data, probably because it needs a codec to convert from text.
 

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
SmartCard readers are commonly used in the military or in government agencies here in the USA, but not nearly as often in private industry. Usually when we had SmartCard activities with the Navy, we had a third-party program that would integrate with other programs (like Access) via a library file or .DLL file that would allow us to just read the results. If you are trying to read the raw results, I have no experience there because I always used the support programs. Sorry but I don't recall details enough to tell you much about them.

When I did a web search for "smart card reader programs" I got several hits including some for which the reader portion was free. You might wish to repeat that search yourself to see if anything useful comes up.
Thanks for response I am still searching.
 

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
Hi. When I tried to manually import your xml file into Access, I got three tables and all text data were there. The only things that didn't import are the photo and signature data, probably because it needs a codec to convert from text.
Hello, thanks theDBguy.
I am still searching and I am totally lost, yes as you said I tried to use the Access wizard and I got the same result.

1. [Photo] and [signature] data, I don't want this both.
2. [MiscellaneousBinaryData] table created while importing NO NEED too.
3. [MiscellaneousTextData] table I need the whole data but unfortunately when I import I got it empty data.
4. [SmartcardData] table I need the whole data.

How can I achieve this to get the specific data as explained above using VBA code?

Thank you so much.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:05
Joined
Oct 29, 2018
Messages
13,130
Hi. I guess the wizard can only extract field values. All the information you want from MiscellaneousTextData are actually attributes, not fields. So, yes, I guess you'll have to use VBA for that. Let me see if I can find a reference for you.
 

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
Hi. I guess the wizard can only extract field values. All the information you want from MiscellaneousTextData are actually attributes, not fields. So, yes, I guess you'll have to use VBA for that. Let me see if I can find a reference for you.
I appreciate your distinguished interaction, I assure you that you will be able to achieve it.

Is the link below can work with me?
Microsoft Access – Using XML Files: Reading and Writing
 

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
Just to let you know dears, I searched the web everywhere still I couldn't get anything can help me to figure out reading specific data from an XML file.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 17:05
Joined
Feb 28, 2001
Messages
18,311

Try that thread to see if it helps you in any way.
 

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
Hi Dears,

I have tried The_Doc_Man link provided but all throughs an error I assume may it's not compatible with my Access 2013.
Shall I upload my Access and XML temp files?
I am struggling a lot.

Dear theDBguy when you get time, don't forget me please.

Thanks in advance.
Need your help
 
Last edited:

Alhakeem1977

Registered User.
Local time
Tomorrow, 02:05
Joined
Jun 24, 2017
Messages
302
Hi,

Kindly read the above post #17

Please find attached, my sample data file and the XML file too.

Your earliest response would be highly appreciated.
 

Attachments

  • Import XML to MS Access.zip
    642.5 KB · Views: 19

Users who are viewing this thread

Top Bottom