By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
--

Ken Snell
<MS ACCESS MVP>

"Danny" <da********@hotmail.com> wrote in message
news:iX**********************@news4.srv.hcvlny.cv. net...
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

Nov 13 '05 #2

P: n/a
"Danny" <da********@hotmail.com> wrote ...
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.

--
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.