We’re in the middle of a development phase and having a hang up with our external process. The data is coming out of Notes using the ODBC SQL 3.02e, when we look at the values returned, all column values have been resorted alphabetically. Not good. When we open the records in access they’re all aligned alphabetical. I can’t seem to find the switch that turns that off. From a formatting perspective it’s a nightmare to try to go through after the fact and line values up so that they make sense.
This may sound like an elementary question, but isn’t it true that retrieving the data is one thing and displaying the data is another?
I will be setting up a server with NotesSQL shortly too, but can you detail the SQL and what the output looks like, and what should the output look like. Also, what client are you using to access the Domino data? Could it be a data conversion problem?
Here are the steps we’re taking, maybe this will make it more clear to people.
Steps:
ceate a new link table in MSAccess
select ODBC as file type
select DSN(data source) for the notes server
select the link table from the list provided
Press Ok
The “Select Unique Tecord Identifier” screen pops up and in that screen is a listing of the column values sorted alphabetical. They are already sorted at this step. The don’t keep their positions when compared to the notes view.
I had a similar problem pulling Excel data into MS SQL…
I resolved it in my case by simply giving the Excel table an alphabetical header row.
This is part of a Microsfot Technote:
CAUSE
OLE DB Provider 4.0 for Jet retrieves columns in alphabetical order rather than in column ordinal position order.
RESOLUTION
To work around this problem, use ODBC Driver for Microsoft Access in conjunction with OLE DB Provider for ODBC. Other OLE DB Providers, such as the OLE DB Provider for SQL Server, do not pose this problem.
If you must use OLE DB Provider for Jet, use the OpenSchema method of the ADODB Connection object, and sort the resultant recordset on the ORDINAL_POSITION field.