I have an MS access database and I cannot read the characters in it
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.
- 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.
ASKER
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
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
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!
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!
ASKER
Dear mdougan
These are valid suggestions, I will try them, but meanwhile could you tell me:
I will read the ascii in both PCs and let you know. Thanks again
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
I will read the ascii in both PCs and let you know. Thanks again
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!
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!
ASKER
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:
Regards
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 (?????????) .
Regards
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.