Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from Excel to Access
My objective of this project is to have my co-workers,
who are not familiar with Access, to input data in Excel. Meanwhile I will either establish a link or a macro to transfer data to Access which, at the end will be picked up by Crystal Report to produce a report. I have made two unsuccessful attempts. ---------------------------------------------------------- Attempt # 1) I made a link between Access and Excel so that when information changed in Excel will change in Access and vice versa. Steps that I used was -From Access, FILE, GET EXTERNAL DATA -Select LINK TABLES. -Choose Excel directory -Choose file type EXCEL -Press LINK -Press NEXT -Name the table name as the table name I had in Access -Press NEXT -Press FINISH However, when I went to Access trying to change my data definition from FIELD PROPERTIES, I realized that I was not able to set any of my fields as Indexed field. As a result, I was not able to work out links in Crystal Report. ---------------------------------------------------------- Attempt # 2) which would be a much better solution if work out. I also tried to achieve such task by using Macro in Excel: Sub ADOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\FolderName\DataBaseName.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub Nevertheless with such Macro I could only transfer the whole Excel database into Access in addition to of what I already (i.e.: duplicate) have instead of updating/ replacing it. ---------------------------------------------------------- Can anyone please give me a solution on either to identify the INDEXED FIELD in Access while linked to Excel or add a command in the Macro in order to avoid duplication when the Macro is run? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from Excel to Access
The reason you can't create the index is that Excel does
not have (natively) indexes for fields (columns) so there is nothing for Access to use when it is a linked table (the index is only good when it exists in the original database, otherwise there is no way to do an indexed lookup). Your second method seems to be no different, really, than importing the table instead of linking it - so why not just use the Get External Data... Import data feature? You could modify your procedure to do what you want (add only new records) but why bother when Access can do all the detail work for you? Both import data and your ADO routine share the same issue of returning all records - even existing ones, but there is a way of dealing with this. Proposed solution: Import into a temporary table, then use an update query (if necessary) to update any records (in the main Access table) that might have been changed in Excel plus an append query to append any new records to the Access table. If you have added the index to the original Access table the update and append will both preserve the index. Hope this makes sense and helps you out... K Dales -----Original Message----- My objective of this project is to have my co-workers, who are not familiar with Access, to input data in Excel. Meanwhile I will either establish a link or a macro to transfer data to Access which, at the end will be picked up by Crystal Report to produce a report. I have made two unsuccessful attempts. ---------------------------------------------------------- Attempt # 1) I made a link between Access and Excel so that when information changed in Excel will change in Access and vice versa. Steps that I used was -From Access, FILE, GET EXTERNAL DATA -Select LINK TABLES. -Choose Excel directory -Choose file type EXCEL -Press LINK -Press NEXT -Name the table name as the table name I had in Access -Press NEXT -Press FINISH However, when I went to Access trying to change my data definition from FIELD PROPERTIES, I realized that I was not able to set any of my fields as Indexed field. As a result, I was not able to work out links in Crystal Report. ---------------------------------------------------------- Attempt # 2) which would be a much better solution if work out. I also tried to achieve such task by using Macro in Excel: Sub ADOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\FolderName\DataBaseName.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub Nevertheless with such Macro I could only transfer the whole Excel database into Access in addition to of what I already (i.e.: duplicate) have instead of updating/ replacing it. ---------------------------------------------------------- Can anyone please give me a solution on either to identify the INDEXED FIELD in Access while linked to Excel or add a command in the Macro in order to avoid duplication when the Macro is run? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from Excel to Access
wrote ...
My objective of this project is to have my co-workers, who are not familiar with Access, to input data in Excel. Meanwhile I will either establish a link or a macro to transfer data to Access such Macro <snipped could only transfer the whole Excel database into Access in addition to of what I already (i.e.: duplicate) have instead of updating/ replacing it. You haven't provided DDL schema (CREATE TABLE) or sample data (INSERT INTO) so I assume you want a general answer. So, you want to create an outer join (e.g. RIGHT JOIN) between you main Jet table and the linked Excel table using the key (your index?) column and test for unequal data columns (for amended rows) and for null values in the key column in the Jet table (for new rows). To additionally test for deleted rows, reverse the join (e.g. LEFT JOIN this time) and test for null values in the key column in the linked Excel table. I have a generous nature on Fridays, so here's a quick demo (with DDL) which, for simplicity's sake, uses one key column and one data column (assumes a connection to the Jet db e.g. execute each statement in a query object in the MS Access UI). Create the Jet table: CREATE TABLE MorrisJet ( MyKeyCol INTEGER NOT NULL, MyDataCol INTEGER NOT NULL ) ; ALTER TABLE MorrisJet ADD CONSTRAINT pk__morris PRIMARY KEY (MyKeyCol) ; Create the Excel table: CREATE TABLE [Excel 8.0;HDR=Yes;Database=C:\Morris.xls;].MorrisXL ( MyKeyCol INTEGER , MyDataCol INTEGER ) ; Populate the Jet table: INSERT INTO MorrisJet (MyKeyCol, MyDataCol) VALUES (1,1) ; INSERT INTO MorrisJet (MyKeyCol, MyDataCol) VALUES (2,2) ; INSERT INTO MorrisJet (MyKeyCol, MyDataCol) VALUES (3,3) ; Now, let's create the Excel data in an 'edited' state: First, a row intact: INSERT INTO [Excel 8.0;HDR=Yes;Database=C:\Morris.xls;].MorrisXL (MyKeyCol, MyDataCol) VALUES (1,1) ; Then an amended row: INSERT INTO [Excel 8.0;HDR=Yes;Database=C:\Morris.xls;].MorrisXL (MyKeyCol, MyDataCol) VALUES (2,999) ; Let's say the MyKeyCol=3 row has been deleted in Excel so we simply won't insert it in the first place. Finally, a new row: INSERT INTO [Excel 8.0;HDR=Yes;Database=C:\Morris.xls;].MorrisXL (MyKeyCol, MyDataCol) VALUES (4,4) ; Now, in no particular order, modify the database using the amended Excel data. First, INSERT the new rows: INSERT INTO MorrisJet (MyKeyCol, MyDataCol) SELECT T2.MyKeyCol, T2.MyDataCol FROM MorrisJet T1 RIGHT JOIN [Excel 8.0;HDR=Yes;Database=C:\Morris;].MorrisXL T2 ON T1.MyKeyCol=T2.MyKeyCol WHERE T1.MyKeyCol IS NULL ; Next, UPDATE the amended rows: UPDATE MorrisJet T1 INNER JOIN [Excel 8.0;HDR=Yes;Database=C:\Morris;].MorrisXL T2 ON T2.MyKeyCol=T1.MyKeyCol SET T1.MyDataCol=T2.MyDataCol WHERE T1.MyDataCol<T2.MyDataCol ; Finally, DELETE the removed rows: DELETE FROM MorrisJet T3 WHERE EXISTS ( SELECT * FROM MorrisJet T1 LEFT JOIN [Excel 8.0;HDR=Yes;Database=C:\Morris;].MorrisXL T2 ON T1.MyKeyCol=T2.MyKeyCol WHERE T2.MyKeyCol IS NULL AND T3.MyKeyCol=T1.MyKeyCol ) ; Jamie. PS Ping Dick: one for the blog? -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel access data from Access?! | Excel Discussion (Misc queries) | |||
Data from Access to Excel | Excel Discussion (Misc queries) | |||
Data from Access to Excel | Excel Programming | |||
data from excel to access | Excel Programming | |||
Excel data to Access | Excel Programming |