Problem with DAO Vs ADO

[Ronald W. Roberts]

I'm writing an application that will import an Excel spreadsheet. The format
of the spreadsheet isn't

always the same, which means the Order Number isn't always in column A.



1.. The program drops or deletes the tblInput table from the collection.
2.. Imports the spreadsheet into a new tblInput.
3.. I have code that does a TabelDef and loops thru the table definition
of tblInput,
putting the field or column names into a table named tblFieldName.
4.. I then load a list box allowing the user to tell the program the Order
Number column.


Now for the problem, if I use DAO everything works fine. The columns in the
spreadsheet match column for column

the order of the tblInput table. This means the index in the collection is
in the same order as the physical columns in the spreadsheet.



If I use ADO, after the import to tblInput table, I start to load the list
box with the field names from the tblInput table.

What happens is the column or field name are returned in alphabetic order.
So what is in column 3 in the spreadsheet

is now the first column or index in the table definition.



I need to be able to load the list box in the same order as the spreadsheet
so the user doesn't get confused about which

column or field name to pick. The reason this is important is because there
is a option on my import routine that allows the user

to tell the program there are no column heading in the spreadsheet. In the
case the field name will be the default names F1, F2, F3 and so on.



Below is the code to read the table def and return the field names.



Dim cat As New ADOX.Catalog



Set cat.ActiveConnection = Application.CurrentProject.Connection

x = cat.Tables("tblInput").Columns.Count

For Y = 0 To x - 1



--Cut Code--



strName = cat.Tables("tblInput").Columns(Y).name



--Cut Code--



Next Y







An example of what is happening in ADO is

The spreadsheet

Columns Field Index

Phy Ord. Name

1 OrderNumber 0

2 LineNumber 1

3 BreakPoint 2

4 Quantity 3



After the import into tblInput, if I run the loop above, it returns the
following in alphabetic order.

Names returned from tblInput using the code above.

Field Name Index or Y

BreakPoint 0

LineNumber 1

OrderNumber 2

Quantity 3



So the question is, How do I get it to return the field names in the same
order there are in the spreadsheet?



Thanks for any help you may provide,



Ron

 

[Piet Linden]

I'm writing an application that will import an Excel spreadsheet. The format
of the spreadsheet isn't

always the same, which means the Order Number isn't always in column A.

  1.. The program drops or deletes the tblInput table from the collection.
  2.. Imports the spreadsheet into a new tblInput.
  3.. I have code that does a TabelDef and loops thru the table definition
of tblInput,
  putting the field or column names into a table named tblFieldName.
  4.. I then load a list box allowing the user to tell the program the Order
Number column.

Now for the problem, if I use DAO everything works fine. The columns in the
spreadsheet match column for column

the order of the tblInput table. This means the index in the collection is
in the same order as the physical columns in the spreadsheet.

If I use ADO, after the import to tblInput table, I start to load the list
box with the field names from the tblInput table.

What happens is the column or field name are returned in alphabetic order..
So what is in column 3 in the spreadsheet

is now the first column or index in the table definition.

I need to be able to load the list box in the same order as the spreadsheet
so the user doesn't get confused about which

 column or field name to pick. The reason this is important is because there
is a option on my import routine that allows the user

to tell the program there are no column heading in the spreadsheet. In the
case the field name will be the default names F1, F2, F3 and so on.

Below is the code to read the table def and return the field names.

Dim cat As New ADOX.Catalog

Set cat.ActiveConnection = Application.CurrentProject.Connection

x = cat.Tables("tblInput").Columns.Count

For Y = 0 To x - 1

--Cut Code--

                strName = cat.Tables("tblInput").Columns(Y).name

--Cut Code--

Next Y

An example of what is happening in ADO is

The spreadsheet

Columns                Field                       Index

Phy Ord.                Name

1                              OrderNumber        0

2                              LineNumber          1

3                              BreakPoint             2

4                              Quantity               3

After the import into tblInput, if I run the loop above, it returns the
following in  alphabetic order.

Names returned from tblInput using the code above.

Field Name            Index or Y

BreakPoint             0

LineNumber          1

OrderNumber        2

Quantity                3

So the question is, How do I get it to return the field names in the same
order there are in the spreadsheet?

Thanks for any help you may provide,

Ron

Maybe I'm being fooled by the red herring of field order. If the
field NAMES are always the same and always exist in the Excel file,
then why not just create an append query that uses the Excel file as
the source?

start here and then turn this into an append query...

SELECT *
FROM Menu
IN "G:\ADH\Ch05\test5.xls" "Excel 5.0;"

Once you have the field list in the Select line in the order you want,
the order of the columns in the Excel file really don't matter.

As you have discovered, field order in ADO is not in "sequential"
order... IOW, not in the order of the columns in the source file. The
only way around it is to use the above...

 

[Arvin Meyer [MVP]]

I hate to ask the obvious, but if DAO works, why aren't you using it?

ADO technology was developed to be used with ADP's and SQL-Server, DAO is
still the preferred technology to use with Access MDBs against any back-end
engine.

 

[Ronald W. Roberts]

This is a prototype of an application to be upsized later using an SQL or
Oracle server.

I don't know if in the future this application will stay an MS Access
front-end or rewritten in something like VB or VB.NET.

Also IT management wants us to move away for DAO and only use ADO. They have
this fear that someday DAO will change or go away. I agree with want your
saying, but that's not the world I live currently in. I'm going to print out
your reply and give it to the boss. It'll be interesting to hear what he has
to say.

Having say that, it still doesn't make any sense that you have a record with
field names Car (0), Bus (1) , airplane (2) and in ADO if I subscript the
tabledef, they come out Airplane (0), Bus (1), Car (2) even though the
physical record layout is Car, Bus , Airplane.

Thanks,

Ron

 

[Douglas J. Steele]

The logic may be that ADO references fields in the Fields collection
alphabetically, not physically.

Must you refer to the fields numerically? Can you not use their names?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Arvin Meyer [MVP]]

While you are talking with your boss, you may want to add this tidbit.
Without violating NDAs I am confident that VBA and DAO will be available for
the next 2 versions of Access. I cannot be absolutely sure about ADO for
more than the next version. ADO has been replaced by ADO.NET. The 2 are not
fully compatible, nor can be made that way. While I cannot put my finger on
the MSDN article at the moment, Microsoft has recently recommended using
MDBs with DAO to access SQL-Server data. There are no guarantees of backward
compatibility, but Microsoft has an excellent record when compared to other
software companies. There may be some future advantage to rewriting in
VB.NET (but I can't think of a good one right now). There aren't any
significant reasons to choose VB over VBA right now, since VB is definitely
deprecated and VBA is still developing new methods and properties.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Ronald W. Roberts]

Yes, if the user imports a spreadsheet with column heading, everything works
out because the use knows the name of the column to use. But if they import
a spreadsheet without column headings, the column name come in as F1, F2, F3
and son. I can see different users haveing trouble figuring out which is the
correct column to select.

 

[Ronald W. Roberts]

Thanks for the information.

While you are talking with your boss, you may want to add this tidbit.
Without violating NDAs I am confident that VBA and DAO will be available
for the next 2 versions of Access. I cannot be absolutely sure about ADO
for more than the next version. ADO has been replaced by ADO.NET. The 2
are not fully compatible, nor can be made that way. While I cannot put my
finger on the MSDN article at the moment, Microsoft has recently
recommended using MDBs with DAO to access SQL-Server data. There are no
guarantees of backward compatibility, but Microsoft has an excellent
record when compared to other software companies. There may be some future
advantage to rewriting in VB.NET (but I can't think of a good one right
now). There aren't any significant reasons to choose VB over VBA right
now, since VB is definitely deprecated and VBA is still developing new
methods and properties.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Douglas J. Steele]

But if the columns are named F1, F2, F3, you can refer to them by name, not
position, can't you?

 

[Ronald W. Roberts]

No you can't. One of the options the user can select is "No Column
Headings".
There are no field name and Excel assigns F1, F2, F3. I'm not sure I can
trust
the user to not be confused about why they are selecting F3 for example.

Because I won't always know the format of the spreadsheet to be imported,
I always drop the table and create it new using only the same table name
when I import a new spreadsheet.
Access with use the F1, F2, F3 for the field names in the table.

As I re-reasd this and am thinking about you have said, it just might work
after all.
If the spreadsheet has no column name, then ADo should show the as F1, F2,
F3, which would be
the physical and alphabetic order of the columns. If the Order Number is in
Column
3, the user only needs to select F3 matching the column number with the last
digit of the field name.

I;m goiing to do some more testing to see if this will work without
confusing the user.

Thanks,
Ron