RDO DataType query.

A colleague is trying to get some SQL Server data into MS Access, and is querying the table from a VB program.

She opens an rdoconnection to an SQLServer database. When testing recordset.rdocolumns("fieldName").type it gives -9 as the value. This does not relate to any of VB's listed RDO connection types. It also shows the rdocolumns("fieldName").size as being twice what the database shows when viewed through Microsoft Access. What is this datatype?

Any ideas?

I've found various lists for datatype eNums for ADO, but nothing on RDO (as it's obviously obsolete). Anyone out there got any ideas (other than "use something else")?

Thanks in advance

Chris.

export the data from sql server using dts

call the dts from the apps using dts run utility

or

use ado

or use replication

Rate Table - Need Help To Display Data Horizontally

Hi everyone. I need help creating a query for a transportation company. I am trying to querying a table of rates that stores it's values vertically and want to display the information horizontally.

Below is an example of sample data from the table. One column in the table lists all the different cities that a group of breakpoints apply to; another lists the different weight breakpoints; and the third contains the corresponding rates for each weight break. See example below.

Weight Range
(not a field in the database
just added to describe meaning
of breakpoint column) City Breakpoint Rate
0 -100 A 100 $100
101 - 200 A 200 $200
201 - 300 A 300 $300
0 -100 B 100 $100
101 - 200 B 200 $200
201 - 300 B 300 $300

I want to display the information horizontally
City 0-100 101-200 201-300
A 100 200 300
B 100 200 300

The only other twist is that different companies have different weight breaks and they are all stored in the same table. For example Company ABC's rates have the following weight breaks.

Weight Range
(not actual field
in database) Breakpoint field Rate
0-100 --> 100 $100
101-200 --> 200 $200
201-300 --> 300 $300

Company XYZ can have breakpoints such as the following.
0-50 --> 100 $100
51-100 --> 200 $200
101-150 --> 300 $300

If creating one report to accomodate both companies is not possible then I could also create seperate reports for each company. Any help or suggestions would greatly be appreciated. Thanks in advance.i would suggest that you do this in the application layer|||I agree with r937. Don't make your SQL statement too complicated, because it will be a pain in the butt to fix or change things later. It would be way easier to loop through the results and place each value in a cell in a table. By doing this you can also do some error checking for bad data before you display.

Good luck
Hope it helps|||This is essentially a "table pivoting" problem.
It's not clear from your example but I'm assuming that the number of output columns can be larger than 4, depending on the input data.
In that case, only recursive SQL (using "WITH", i.e., CTEs) will help you. (Or maybe your SQL engine has a built-in PIVOT functionality ...)

See http://tinyurl.com/6wugk for a related problem (with solution).

H.t.h.