Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Access from Excel Macro
working with Excel macro to create a tracking system, the
types of reports and results required are easiest performed in excel however the amount of data really should be in a database ie. Access, I know how to query the information into a sheet, but have been unsuccessful at getting an update to the database to work from the macro Where can I get some information on how to do this, or how do I accomplish this? VBA Help has been very little help and confussing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Access from Excel Macro
You would most likely use the ADO library to do this. Set a reference to
Microsoft ActiveX Data Objects. Create a Connection object using the Microsoft.Jet.OLEDB.4.0 provider with the path to your database as the data source. Then create Commmand Objects with the CommendText property set to an SQL string for INSERT or UPDATE queries. Create paramaterized commands assigning the values from the worksheet cells to the Paramaters then execute your queries. This would resemble something like this: // Module Level Declarations or as public main module variables Private cn As ADODB.Connection Private cmd As ADODB.Command // Created before calling update or insert functions Dim cnStr As String cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\ProductionSchedule.mdb" Set cn = New ADODB.Connection cn.Open cnStr // Called with values from Excel cells Private Sub UpdateCatData(ByVal catKey As Long, strCat As String, strNotes As String) On Error GoTo ErrHandle Dim strQry As String strQry = "UPDATE Product_Categories SET Product_Category = @Cat, Notes = @Notes WHERE Product_Category_Key = @CatKey" Set cmd = New ADODB.Command With cmd .ActiveConnection = cn .CommandText = strQry .CommandType = adCmdText .Parameters.Append .CreateParameter("@Cat", adVarChar, adParamInput, 50, strCat) .Parameters.Append .CreateParameter("@Notes", adVarChar, adParamInput, 250, strNotes) .Parameters.Append .CreateParameter("@CatKey", adInteger, adParamInput, 4, catKey) .Execute End With Exit Sub ErrHandle: // Error handling removed for clarity End Sub // called with values from Excel cells Private Sub InsertCatData(strCat As String, strNotes As String) On Error GoTo ErrHandle Dim strQry As String strQry = "INSERT INTO Product_Categories (Product_Category, Notes) VALUES(@Cat,@Notes)" Set cmd = New ADODB.Command With cmd .ActiveConnection = cn .CommandText = strQry .CommandType = adCmdText .Parameters.Append .CreateParameter("@Cat", adVarChar, adParamInput, 50, strCat) .Parameters.Append .CreateParameter("@Notes", adVarChar, adParamInput, 250, strNotes) .Execute End With Exit Sub ErrHandle: // Leaving error handling in do demonstrate ADO Errors collection (this is not quite how you would do it from Excel!) Dim errObj As Error Dim strErr As String Dim idx As Integer Dim errs As Errors On Error Resume Next idx = 1 If Not cn.Errors.Count 0 Then strErr = "VB Error # " & Str(Err.Number) strErr = strErr & vbCrLf & " Generated By " & Err.Source strErr = strErr & vbCrLf & " Description: " & Err.Description Else Set errs = cn.Errors For Each errObj In errs With errObj strErr = strErr & vbCrLf & " Error # " & idx & "; " strErr = strErr & vbCrLf & " ADO Error # " & .Number strErr = strErr & vbCrLf & " Description: " & .Description strErr = strErr & vbCrLf & " Source: " & .Source idx = idx + 1 End With Next errObj End If Err.Raise 5858 Or vbObjectError, "InsertCatData", strErr End Sub -- Graham (My favourite ADO reference is the Rob Mac Donald book Serious ADO from APress!) "David Slicer" wrote in message ... working with Excel macro to create a tracking system, the types of reports and results required are easiest performed in excel however the amount of data really should be in a database ie. Access, I know how to query the information into a sheet, but have been unsuccessful at getting an update to the database to work from the macro Where can I get some information on how to do this, or how do I accomplish this? VBA Help has been very little help and confussing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Access from Excel Macro
"David Slicer" wrote ...
Access, I know how to query the information into a sheet, but have been unsuccessful at getting an update to the database to work from the macro Post the code you are using to fetch the data. Jamie. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Access from Excel Macro
-----Original Message----- "David Slicer" wrote ... Access, I know how to query the information into a sheet, but have been unsuccessful at getting an update to the database to work from the macro Post the code you are using to fetch the data. Jamie. -- . The code I use was generated through record macro and using the external data new query With ActiveSheet.QueryTables.Add(Connection:=Array (Array( _ "ODBC;DSN=MS Access Database;DBQ=H:\dvctracking\DVCTracking.mdb;Defaul tDir=H:\d vctracking;DriverId=25;FIL=MS Access;MaxBufferSize=204" _ ), Array("8;PageTimeout=5;")), Destination:=Range ("A1")) .CommandText = Array( _ "SELECT HomeBase.HomeBase" & Chr(13) & "" & Chr (10) & "FROM `H:\dvctracking\DVCTracking`.HomeBase HomeBase" _ ) .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating excel worksheet from an Access Query | Links and Linking in Excel | |||
Updating Records from MS Access | Excel Discussion (Misc queries) | |||
Updating Excel forecasts into an Access Database | Excel Worksheet Functions | |||
Access records updating from Excel. | Excel Programming | |||
updating Access table with Excel data | Excel Programming |