Follow up to previous post: Need code to import into access/Autonumber

[Danny]

I was trying to tihnk of solutions to the table in access being out of order
when i use:
DoCmd.TransferSpreadsheet in code to import an excel file.


1. I could read the excel file from access. Does anyody have access code
to open an exel sheet and read it into a table in access in code?

2. have the user maintain an autonumber like field. THis way, I can have
this to sort on when I import it. Does excel have an autonumber field?

Thanks

 

[Ken Snell]

Some sample code for reading EXCEL spreadsheet and writing to recordset that
may get you started:



Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing

 

[Jamie Collins]

I was trying to tihnk of solutions to the table in access being out of order

I assume that by 'table in access' you mean a table in a Jet database.

The physical (e.g. on disk) ordering of a database table is determined
by its clustered index. The performance advantage of the clustered
index (physical order) is obvious e.g. think of how a paper copy
telephone directory is ordered by last name, first names.

The clustered index is a fundamental concept of table design which Jet
does not expose to the user e.g.

CREATE CLUSTERED INDEX my_index ON MyTable (MyKeyColumn)

will fail with a syntax error, remove the CLUSTERED and it is OK.
However, Jet tables do of course have a clustered index, being the
primary key (PK). This is why an IDENTITY/autonumber (or a homemade
incrementing integer) column is never a good choice for PK; imagine
now a telephone directory ordered by telephone number. If the IDENTITY
column is required to enforce uniqueness for the sake of it, define
your PK column as (meaningful_text_column, IDENTITY_column).

Also bear in mind that a Jet clustered index is only rebuilt when the
database file is compacted. Any rows added to the table subsequent to
the last compact will be added unordered to the 'bottom' of the table.

Jamie.

--