Solved

I have an MS access database and I cannot read the characters in it

Posted on 2009-03-29
9
759 Views
Last Modified: 2013-12-25
I have a MS Access database, with some arabic data stored in it. The data were saved by running my VB6 application in computer A. The data behaved as follows:
- when reading it from the VB6 application on PC A it is readable
- when opening MS access and reading it it shows as graphical characters
- when reading it using the same application on another PC B it shows as set of question marks.
- when open the MDB file in the PC 2 it still show as graphics.

If I change the data manually in the database, the applications on both PCs read it as question marks, although they are readable in the MDB itself.
I know the whole thing is related to ANSI, and unicode, but I do not understand this.
Can some one help me to know how to Read the data in both the MDB and the VB6 application in corerct arabic. Please be very specific and detailed as The concept of encoding is vague to me.

Thanks you very much.
0
Comment
Question by:fmichail
  • 5
  • 4
9 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 24018898
Are both PC's running the same operating systems?  I know that there were differences between unicode between 16/32/64 bit operating systems.

Do both computers have the same language packs installed?  If you google browse to a website with Arabic do you see the same thing on both computers?

I suspect that you won't be able to change the data manually through Access as Access's editors probably do not support the extended format necessary for the Aribic codes... when you are updating the data through your program, you are simply sending binary data to the database and it will store it exactly encoded as you sent it... but through the database editors, Access is encoding it using the regular ASCII character set.
0
 

Author Comment

by:fmichail
ID: 24025000
Dear mdougan,
both computers run xp
arabic sites show the same ways in both computers
If I save from computer (A) to the database, I see what I saved in both Access, and the VB program correctly in computer (A)
If I try to see what I saved in computer A in another computer (B) I do not see arabic in ACCESS, and I do not see arabic when running the same application on (B)
The other way
If I save data using the application in (B) I read arabic in the application when running on (B), but I see graphics if I look in ACCESS in (B)
If I try to see the data recorded on B using A I do not see arabic, neither in the application nor in ACCESS
Data type               See in ACCESS (A)     See in VB (A)   See ACCESS (B)     see VB in (B)
saved by app in (A)         Yes                                 Yes                          NO                         NO
Saved in Access (A)        Yes                                 Yes                          No                          No

saved by app in (B)         No                                   No                          NO                         Yes

Saved in Access (B)        No                                  No                          Yes                          No
It looks like ODBC is different between the 2 PCs
Is there a way to define the type of encoding when I want to read data using an adodb connection?.
The best scenario is if both PCs act like PC (A) where I can read in access the data saved using the VB application.
Thanks
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24030305
The first two scenarios would make me think that you do not have the same language pack installed on PC B.  A language pack interprets certain extended ascii codes into certain special characters.  But, the fact that you can see the characters on PC B at all is confusing, as you wouldn't be able to if you didn't have some language pack installed.

What you might try is writing a temporary function in your application to display the ascii value of the characters, then compare the ascii values input from PC A and from PC B.  I'm betting that you find that values input from the two computers for the same characters are different.

The VB function that returns the ascii value is ASC.  So, you would put a single character in a string and then copy it to a LONG...

Dim mycode as long
Dim mystring as String

mystring = .... some character from the database
mycode = ASC(mystring)
msgbox mycode

Definitely try to get the same version of ODBC on PC B as on PC A, as that could make a difference.  Also, if you have defined an ODBC DSN on both computers, ensure that you are using the same ODBC Driver on both PCs.

I don't know if there is a way to define the type of encoding with adodb... it might mean using a different data type... but with Access, I don't think you have too many options... with SQL Server, you have NVARCHAR which I think is the unicode version of VARCHAR, but Access doesn't have that.

If none of this helps, I'm all out of suggestions!  Good luck!
0
 

Author Comment

by:fmichail
ID: 24034204
Dear mdougan
These are valid suggestions, I will try them, but meanwhile could you tell me:
  • How to know the odbc version
  • How to install a different ODBC version
By the way, I copied/installed all the fonts from the PC (A) to PC (B), but the problem is as is.
I will read the ascii in both PCs and let you know. Thanks again
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 18

Expert Comment

by:mdougan
ID: 24097977
As far as ODBC version, it is more important knowing which ODBC driver you're using on each PC.  In your program, you might have a connect string to your database with a parameter:

DSN=MyDatabaseDSN

That is the Data Source Name that you've defined using the ODBC Admin tool under Administrative tools.  What you should do is (on both PCs), open up the ODBC Admin tool, then you will see all the DSNs listed there.  Look for the one with the same name as is used in your connect string, then look to the right to see what driver is selected.  See if it is the same on both computers.

Next, click on the Drivers tab, and look for the name of the driver, and to the right, it will show what driver version you are using, make sure both computers are using the same version of the same driver.

It is possible that you have not created an ODBC DSN on the computers, but instead have added a:

PROVIDER='Microsoft Access Driver (*.mdb)',....

(or whatever your Driver name is)  to your connect string... if so, then you have to search your registry for that exact driver name, and you should find another registry entry that specifies what the version number is.

Cheers!
0
 

Author Comment

by:fmichail
ID: 24119052
Dear mdougan
I am sorry for being late in follow up, please accept my apology.
Nothing works so far, however, here is the summary of what I have now:
  • I am using data in Excel file (Shows correctly in computer A and B)
  • Computer A has Microsoft Access user DSN in both A and B
  • The ODBC driver version is the same in A and B, and using same dll (even datetime stamp is the same)
  • Both computers has the same copy of Office 2003
  • Both computers use the same version of XP professional 2002 and service pack 2
  • Computer A reads correct excel data in VB, but computer B reads same data from the same file as unreadable data (?????????) .
It is really frustrating. Please help
Regards
 
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24119614
no worries, take as much time as you need to follow up.

You are very thorough in checking all possibilities!  Even though it may feel as if you are not making any progress, you are at least eliminating many possibilites, and so, eventually, we must arrive at the right answer.

I will do more research on the arabic character set to see if there are any special things that have to be configured on a computer to get them to display properly.

In the meantime, I have a couple of additional questions for you.

When you say that the data appears in your VB program (on PC B) with all ??????? what are you using to display the data in your program?  A grid of some sort?  A textbox?

If I understand correctly, you are reading arabic characters in from an Excel file, into the VB program... how are you doing this?  Are you creating an Excel Application object then opening a Workbook object?  Or are you using some other method?

Then, you are using ODBC to open a connection to an Access database, and you are writing the Arabic characters there.  But, when you write from PC B, you have the unreadable data.

If I am correct in all of those assumptions, the problem sounds as if it is due to the general preference settings in Excel.  I had similar problems with Dates on two computers.  The general preferences were for displaying dates in different formats on the two computers and the way I was passing the data in, couldn't be interpreted as a date on PC B the same way it was on PC A.   When Excel cannot figure out how to display a value, it puts ?????? in it's place.

When you are accessing the cell data, are you getting the cell text, or the cell value?  It might be that accessing the cell value will have the correct ASCII codes even if Excel would be displaying the ????

Keep trying, I think we will find a solution for you!
0
 

Accepted Solution

by:
fmichail earned 0 total points
ID: 24127320
Dear mdougan,
I think I found the solution finally, here is what I did:
I checked the regional settings of the 2 computers, and found some differences:
  • From Languages TAB, then click [Details] button, then select Settings tab and in Installed services select Arabic (Egypt) from the drop down list and select code page 102
  • In Languages tab select Arabic (Egypt) from the drop down list of languages for non-unicode programs
  • In the same tab check the following code pages (10004 MAC Arabic / 708 Arabic-ASMO / 864 OEM Arabic)

and it worked OK, and to tell you the truth, I do not understand the explanation of how did these settings fix the problem, but it did.
You are so ethical, committed, and knowledgeable, and it is my pleasure to have these communications with you. Thanks again
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24127471
Hi fmichail,

That is great news!  I don't know that I can follow all of that, however, it does make some sense... I think that when you select various code pages from the Regional Settings, it downloads something that can interpret the extended ASCII codes related to the special characters in the arabic character set.  Early on, I suspected as much, but didn't know where to tell you to look!

Thank you for the compliment, however, I'm happy to try to help as much as I can, and it is as much my pleasure as it is yours.  In this case, you've found your own solution, so, please, feel free to ask to close the question and have your points refunded.

Cheers!
Michael
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now