If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to create graphs in a monthly report where the base data can change | John Clarke | Charts and Charting | 3 | June 25th, 2004 02:22 AM |
converting files from microsoft works to excel and/or access data base | reggie | General Discussion | 3 | June 22nd, 2004 08:24 PM |
Using VLookup to Import Data from Access to Excel | Westie_Lover | Worksheet Functions | 1 | June 21st, 2004 10:56 PM |
Excel VBA-Geting data from Access | kodricales | New Users | 2 | May 27th, 2004 09:23 PM |
Getting data into Excel cells for OLE objects retuened from Access | Raj | Charts and Charting | 1 | January 16th, 2004 12:37 PM |