Updating data from excel to access
I have a access dbase with patient #, name, and a deceased date. Please help I just manually typed in over 200 deceased dates.
Follow the wizard steps to select the sheet or named range containing the Excel table that you want to link.I extract data from my Access database into an Excel worksheet using a macro. Also there is a primary key, so I would just need to map on that field. Can I load the whole excel datasheet into a recordset and run some snazzy update query? Cells str SQL = "UPDATE table SET " str SQL = str SQL & "Field1 = " & Chr(34) & row.I first open a connection to the database, define my sql statement in a string var and then dump that data in a recordset: Dim db As Database Dim rs As Record Set Dim sql As String Dim db Location As String db Location = This Workbook. Cells(1) & Chr(34) & "," str SQL = str SQL & "Field2 = " & Chr(34) & row.About EDC Contact Feedback VBA programming Add-Ins ADO/DAO Charts Dialogs/User Forms Display/Statusbar Events Files/Folders Import/Export Menus/Toolbars Misc. OLEDB.4.0; " & _ "Data Source=C:\Folder Name\Data Base Name.mdb;" ' open a recordset Set rs = New ADODB. Open "Table Name", cn, ad Open Keyset, ad Lock Optimistic, ad Cmd Table ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r). Connection ' select one or more files fn = Application.tips OLE Automation Operating System Printing Private Profiles Sound VBA File Access Visual Basic Editor Workbooks Worksheets Worksheet Functions Adding Counting Date Lookup Misc. Formula) 0 ' repeat until first empty cell in column A With rs . Get Open Filename("Excel-files,*.xls", _ 1, "Select One Or More Files To Open", , True) If Type Name(fn) = "Boolean" Then Exit Sub ' connect to the Access database Set cn = New ADODB. OLEDB.4.0; " & _ "Data Source=C:\Folder Name\Data Base Name.mdb;" On Error Go To 0 If cn. Use ADO if you can choose between ADO and DAO for data import or export.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft Active X Data Objects x.x Object Library.
HTH BTW: It is proper Lounge etiquette to start a new thread with a new question and not add on to an existing one.
Well, since this appears to have evolved into an old guys thread, I'll chime in (not sure who is older, but I've been around this forum for well over a decade).
however I would look at why you you are holding two 'base' data sets.
and maybe just have one and refer to it from both systems." id="ctl00_m_m_i_ctl00_gr_ctl03_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="3653759" / As someone else mentioned, having just one source is much preferred to having two "copies" that have the potential of getting out of sync." id="ctl00_m_m_i_ctl00_gr_ctl05_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="3653787" / That works, as does linking an Excel spreadsheet to the Access table.
I'm using Access 2003 This is a general outline of what needs to be done. Update: Ok I've tested, using Excel 2007 but will be basically the same for all versions, and added graphics to show the entire process. I have an Access (2010) tbl Bookings with a Date Completed field.