Linked Database - Force Data Types using VBA

[CopperWire]

I'm linking from Access to an ODBC database and the datatype for a field is a number. When I view the design of the table in Access the datatype is text. Can I use VBA code to link this table and keep the data type as a number?

 

[vbajock]

Just to clarify, you are saying that the ODBC database is showing the field type as a number, but somehow Access shows it as text? Sounds like a bug. Do you have the latest version of the ODBC driver supplied by the db vendor?

I don't think there is anyway to force the datatype to something else via VBA/ADO/DAO as data type negotiation is all handled internally within the ODBC driver. That is all vendor-implemented and usually inaccessible via VBA. You could use the various conversion and format functions to recast the data as you come acrossed it in your code.

 

[VBslammer]

You can't modify linked table design since what you're seeing in Access is a direct translation of the external table's design. I've never encountered a mismatch like that.

Are you sure you're not assuming a field like "CustomerID" is a number when it's actually text?

VBSlammer

Unemployed in Houston, Texas

 

[Hap007]

Dear Copperwire,

Try refreshing the linked (ODBC) tables.

When you first link a table, through ODBC, the properties of the fields are retrieved.

Now, if you modify a field type (say in a SQL backend), Access will not see that modification to the SQL table unless you refresh the link. (When using an ODBC connection)

I will bet this might be what you are encountering.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[CopperWire]

Thank you everyone for your help!

We have been recasting the data and that works, but is a pain. I don't understand why the data type is converting to text! I went back and double checked and the data type is a number in oracle. We are linking several other tables and this doesn't happen.

I tried refreshing the table and that didn't help. The Driver we are using to link to the oracle table is Microsoft ODBC Oracle 2.575.1022.00. Is this the correct driver?

 

[vbajock]

Check this web page:

http://otn.oracle.com/software/tech/windows/odbc/index.html

These are Oracle supplied drivers. They may work better than the MS Drivers.

 

[vbajock]

Dude: Here is another forum you might want to join, dedicated to Oracle ODBC:

http://forums.oracle.com/forums/forum.jsp?forum=145

 

[Hap007]

Dear Copperwire,

One more thought/attempt:

Try deleting the linked (ODBC) table in question from your access program.

Now, re add the linked table and see if that makes a difference.

Later,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[CopperWire]

I went to the other forum and found my answer.

When the field in Oracle is defined as "NUMBER", the fieldtype in Access will be TEXT.
When the field in Oracle is defined as "NUMBER(10)", the fieldtype in Access will be NUMBER(10).

I will change the field in oracle and it will fix my problem. Thanks for all your help!!