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? |
#2
|
|||
|
|||
If you are only using Excel as a data-entry medium, it might be as simple to
make an Access application which looks and acts like those parts of Excel. An Access datasheet form already looks like an Excel spreadsheet. All you have to do is supply a custom menu bar with the editing features you want on it, and make it act as close as you like to the Excel one. Then you get the full power of Access for the data handling, without going through all the Excel-Access hoops. -- Regards, Adrian Jansen J & K MicroSystems Microcomputer solutions for industrial control wrote in 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
|
|||
|
|||
wrote ...
My objective of this project is to have my co-workers, who are not familiar with Access, to input data in Excel. See my reply to your same post in microsoft.public.excel.programming. Jamie. -- |
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 |