UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Importing Excel Spreadsheets generating a primary key for the table, Any Version    
 
   
Massimo
post Jun 23 2010, 08:58 AM
Post #1

UtterAccess Member
Posts: 34



Hi I am trying to import a series of excel spreadsheets into excel 2007. If i use the wizard i can import a spreadsheet with access adding a primary key to the table. i am trying to get this done in vba using transferspreadsheet but i am not getting the result. I need this to be able to reflect the sequence of the rows in the spreadsheet exactly as they are.

i have tried to use DoCmd.RunSQL "ALTER TABLE yourNewTable ADD COLUMN RowNum COUNTER(1,1)" but while this works the records in the table are not in the correct sequence as the record 1 is not the row 1 in the spreadsheet.

Does anybody know how i could do this?
TIA Massimo
Go to the top of the page
 
+
Roger_Carlson
post Jun 23 2010, 09:08 AM
Post #2

UtterAccess VIP
Posts: 2,337
From: West Michigan



nnot rely on an autonumber to maintain an unbroken sequence. The actual value of the autonumber should be of no consequence to you. If it is, you're using it incorrectly.


--------------------
Go to the top of the page
 
+
GroverParkGeorge
post Jun 23 2010, 09:10 AM
Post #3

UA Admin
Posts: 22,028
From: Newcastle, WA



Hi, there should NOT be any correspondence between ordinal position of records in a table and the Primary Key. The ONLY purpose of a Primary Key is to provide a unique identifier for each record. Several observations are pertinent here.

You now have an ACCESS table, in a relational database. That means the restraints imposed by a spreadsheet are no longer applicable. In Excel, ordinal position of rows does matter, but that problem is gone in a relational table.

In a table, relative position is not relevant because you can sort and filter the records in any order you want. Think of it as a bucket in which all of the records bob and float around.

If you want to reflect the sequence of the rows, then you need to do so via a query that SORTS on the relevant field in the table. What field or fields (column in the spreadsheet) in the records determines the proper sequence? Use that field or those fields in sorting the records in your query.

So, in your case, if the ONLY way to know how the sort order should apply comes from their ordinal position in the original spreadsheet, and you have no other way to sort them properly, you are going to have to import them with the Primary Key being part of the import. However, it seems to me that whatever principle is used to order the rows in the spreadsheet should be identifiable and therefore should be available to you in Access as well.

HTH

George


--------------------
Go to the top of the page
 
+
Massimo
post Jun 23 2010, 10:01 AM
Post #4

UtterAccess Member
Posts: 34



Thanks fo ryour replies. I need the records in the Access table to be in a specific sequence becouse i then read them in VBA to update other tables. This is what the table looks like after the import using the transferspreadsheet method in vba
Sample table begins here -------------------------------------------------------------------------------------------
#N/A
#N/A
#N/A
#N/A
#N/A
Site 1 Sun Coast
Building 1 Main
Floor 1 Ground Floor
Room No 1 Exec
Room Lenght 2.30 Meters
Room Width 3.50 Meters
No of Components Inventoried Component Category Component Type Consumption (kWh)
1 Airconditioners Airconditioning 12K BTU 2
2 Fluorescent Lighting Tube 150 cm 0.05
3 Boilers Kettle 2
4 Fridges Bar Fridge 0.4
5 Desktop Computers Full Tower 0.48
6 Desktop Computers Laptop 0.1
7 Fax Machines Fax Machine 0.25
8 Dvd Players Dvd Player 0.15
9 Television Sets Led TV 0.8
#N/A
#N/A
#N/A
#N/A
#N/A
Sample table ends here -------------------------------------------------------------------------------------------------------

I need to be able to take programmatic action knowing that i am at the beginning of the table(read it as spreadsheet) so that i can move each field as required as well as move/skip lines as required. Having a primary key in the table as appended by the manual import gives me the right layout which mimics exactly the look of the spreadsheet.

is there a way to do it?
regards
Massimo
Go to the top of the page
 
+
GroverParkGeorge
post Jun 23 2010, 10:08 AM
Post #5

UA Admin
Posts: 22,028
From: Newcastle, WA



Let's not confuse a sequential ordering value with a primary key. What you have here is NOT a relational table. It is not going to have a Primary Key for that reason. You can append an Autonumber field which will create unique series of numbers. For the most part, they do fall sequentially, but there ought to be better way.

Actually, what I would try to do first would be to LINK, rather than import the spreadsheets and then be very careful not to sort the linked spreadsheets. That would allow you to read the rows out of the linked spreadsheet from top to bottom and process them as you wish.

Would that work?

George


--------------------
Go to the top of the page
 
+
Massimo
post Jun 23 2010, 10:39 AM
Post #6

UtterAccess Member
Posts: 34



Hi George
Thanks for that.
I agree the table is not relational and it is not supposed to be because it simply should be an exact replica of the spreadsheet. My work flow is as follows: import the spreadsheet, process the necessary updates, archive the imported spreadsheet and finally process the next spreadsheet so i need to count on the success of each step, youare suggesting to link the spreadsheet, that is interesting i have never done that way i will look into it. Without ignoring what has been said before, I do not understand why in the manual transferspreadsheet wizard you can get access to generate a primary key and why you cannot in vba, do you have any idea if there is a way around it?
Regards
Massimo
Go to the top of the page
 
+
GroverParkGeorge
post Jun 23 2010, 02:38 PM
Post #7

UA Admin
Posts: 22,028
From: Newcastle, WA



I've not tried to do add a primary key to an imported spreadsheet, and in particular I would not do so with one which has the characteristics yours does. Nor have I read anything pertaining to it that, so I would have to do some additional research to come up with a suggestion.

Perhaps someone else has some insights.

George


--------------------
Go to the top of the page
 
+
Roger_Carlson
post Jun 23 2010, 02:52 PM
Post #8

UtterAccess VIP
Posts: 2,337
From: West Michigan



The trouble appears to be you are adding the autonumber field after the fact.

Well, perhaps you could create the table in SQL code (with an autonumber field) and then import it to the created table.

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "CREATE TABLE PUBLISHERSQL "
strSQL = strSQL & "(PubID Counter(1,1) CONSTRAINT PrimaryKey PRIMARY KEY, "
strSQL = strSQL & "PubName TEXT(100), "
strSQL = strSQL & "PubPhone TEXT(20));"
'...etc

db.Execute strSQL
DoCmd.TransferSpreadsheet acImport, 10, "PublisherSQL", "C:\mypath\myspreadsheet.xls", False, ""


--------------------
Go to the top of the page
 
+
Massimo
post Jun 24 2010, 02:50 AM
Post #9

UtterAccess Member
Posts: 34



Hi Many thanks for that.
I have found a way around so far by coding the line numbers in the spreadsheet so that by sorting using the line no i can recreate the row sequence as it appears on the spreadsheet. Not what i was hoping for but it worked.
I did try a similar solution to what you are suggesting essentially rather than creating a new table every time a spreadsheet was read, i tried to add records to an existing table, Access names a generic fields as Field1, Field2 etc when you specify no header line for a new table, however when you add to an existing table it randomly uses some of the data as field names and it replaces the Field1, Field2 with F1, F2 etc. as a result the transfer Spreadsheet failed because it couldn't match the incoming fields to the fields of the table.
It seems so strange to me that a feature of the Wizard is not available in vba. This is not a new feature of the wizard either as it has been part of it at least since Access 2003.

Regards Massimo
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 22nd July 2014 - 12:25 PM