Update Query Problems

[dhersch]

I have been trying to get data automatically updated from a spreadsheet into my database for some time now. Each spreadsheet has pricing information on it and this data directly relates to a record that already exists in the database. The method I have been trying for some time now is to download the data from the spreadsheet into a temp table in the database. This works fine. . . . I am then trying to take the data in the temp table and use either an update query or an append query to get these particular fields into my main table. For some reason I cannot figure out which query is correct and why I can't get the data to transfer tables. I get key violation errors when I use the append query and when I try the update query I can't reference the other table in the "update to" section. I can't find any other posts that are similar and I really need some help!! Please let me know if there is any assistance you can provide. Thanks.

 

[Norie]

Have you tried just linking the spreadsheet?

If you do that Access will regard it as a table. provided the data is well formed.

In the Append query are you trying to append to any Primary Key fields?

 

[dhersch]

I don't know about linking the spreadsheet. . . I really just want to upload this data and not have to deal with the spreadsheet anymore. For example, I might want to do this for 30 spreadsheets a day. This is my solution to eliminate manual data entry.

In the append query the table I am appending to does have a primary key. I am not trying to create a new record, I am using this method to select the current record I am trying to append to in my form. . .

Forms!Form Name! Autonumber

Please let me know if you need any more information to help. . . .

 

[Norie]

To import/link data to Access goto File>Get External Data...

Do these 30 worksheets have data layed out in the same way?

You could set up links to all of them then create queries to summarize the data.

Are you actually adding new records or amending existing records?

Is there a link between the data on the worksheets and that in the database?

when I try the update query I can't reference the other table in the "update to" section

Is it not listed?

 

[dhersch]

This database is used to house a lot of financial data for projects. All the data does come in the same way, so linking is not a problem, my problem is that the records already exist when I am adding the financial data. I simply want to figure out a way to have access take certain fields in a spreadsheet and upload them into my database for certain blank fields. There are thousands of records and I need to make this simple enough that I can click a button and data goes where it needs to. . . in the past I have used update queries to add data to blank fields, however I never pulled this type of data from a different table than the one I was updating to. . . . For example, in the past I took a date and added "x" to it to automate timelines.

I would try to link the spreadsheets, however I don't want to have to do this every time. I am only pulling off 9 records for each spreadsheet I use. This is only to get rid of the wasted time and effort while eliminating data entry errors.

 

[Norie]

I would try to link the spreadsheets, however I don't want to have to do this every time

You only need to link a worksheet once.

 

[mdmilner]

Here's something you can consider. These code functions were really written to extract specific data from a sheet and import it into an Access database to compare to other data imported from elsewhere. It may be adaptable to your needs.

If this isn't clear enough, you can go somewhere like here for more technique explanation.

http://www.mvps.org/access/modules/mdl0035.htm

Mike

At the top of a new code module, put this first before anything else:
These are global variables.

Dim objWkb As Workbook
Dim objXL As Excel.Application

All in the same module as the above, drop these below (any order)
Call GetXLSData by passing it the full path to the spreadsheet and the name of the sheet you would like to use (or create if necessary)

Function GetXLSData(ByVal sSrcFile As String, ByVal strSheet As String)

Call OpenXLS(sSrcFile, strSheet, "Begin")
Call ExtractData(strSheet)
Call EndXLS(sSrcFile)

End Function

Sub OpenXLS(ByVal sSrcFile As String, Optional ByVal strSheet As String, _
             Optional ByVal strMode As String)
Dim objSht As Worksheet

' Open Excel and the specific spreadsheet
If strMode = "Begin" Then
 Set objXL = New Excel.Application       ' Start New Excel
 objXL.Visible = True              'visibility
 On Error Resume Next
 Set objWkb = objXL.Workbooks.Open(sSrcFile)
 If Not Err.Number = 0 Then
   ' Create the Workbook
   Set objWkb = objXL.Workbooks.Add
   Err.Number = 0
 End If
End If
 On Error Resume Next
 If Len(strSheet) > 0 Then
   Set objSht = objWkb.Worksheets(strSheet)
   If Not Err.Number = 0 Then
     Set objSht = objWkb.Worksheets.Add
     objSht.Name = strSheet
  
     Err.Number = 0
   End If
 End If
 Err.Clear
 On Error GoTo 0

Set objSht = Nothing
End Sub

Function ExtractData(ByVal strSheet As String)
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim objSht As Worksheet
Dim intRng, x As Integer
Dim tdf As DAO.TableDef
Dim strSQL As String
Dim fld As Field

Set dbs = CurrentDb()
Set objSht = objWkb.Worksheets(strSheet)
With objWkb
  intRng = Range("A65536").End(xlUp).Row - 1         ' This gets the Grand Total Row
  intRng = Range("A" & intRng).End(xlUp).Row         ' This gets the actual end row
  
  ' If it doesn't exist, create it.  If it does, clear the contents
  If Not ObjectExists("Table", "Greenbill") Then
    Set tdf = dbs.CreateTableDef("Greenbill")
    tdf.Fields.Append tdf.CreateField("ClaimNum", dbText, 10)
    tdf.Fields.Append tdf.CreateField("Amount", dbDouble, 10)
    dbs.TableDefs.Append tdf
  Else
    strSQL = "DELETE * FROM Greenbill"
    DoCmd.RunSQL strSQL
  End If

  strSQL = "SELECT * FROM Greenbill"
  Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
  
  ' Loops through the spreadsheet and adds the data to a table ("greenbill")
  With rs
    For x = 5 To intRng
      .AddNew
      If Left(objSht.Cells(x, 2), 1) <> 0 Then
        .Fields(0).Value = "0" & objSht.Cells(x, 2)
      Else
        .Fields(0).Value = objSht.Cells(x, 2)
      End If
      .Fields(1).Value = objSht.Cells(x, 5)
      .Update
    Next x
  End With
End With

Set tdf = Nothing
Set objSht = Nothing
Set rs = Nothing
Set dbs = Nothing
End Function

Sub EndXLS(ByVal strLoc As String)

' Closes the Excel Object

On Error GoTo HandleErr
  objXL.DisplayAlerts = False
  objXL.UserControl = True
  objWkb.Close True, strLoc
  objXL.DisplayAlerts = True
  objXL.Quit
  
Set objWkb = Nothing
Set objXL = Nothing
ExitHere:
    Exit Sub

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 08-31-2004 08:43:06   'ErrorHandler:$$D=08-31-2004    'ErrorHandler:$$T=08:43:06
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "modAssemble.EndXLS" 'ErrorHandler:$$N=modAssemble.EndXLS
    End Select
' End Error handling block.
End Sub