0

I have a problem with updating an existing Access table. I have an Access database that's supposed to sort some .log files with two columns: filename and filedate. I have the filename and filedate in an Excel file that I have made from sorting through 100+ .log files, so there are 100+ files that should be linked to Access

Accessenter image description here

I get an error every time and have tried many different things. The problem is that I don't know how to write the syntax. Can someone help me with that please? I want A -> filename and B -> filedate so that every pair gets its own ID

import pypyodbc


UDC = r'C:\Users\Kaiser\Documents\Access\UDC.accdb'
# DSN Connection
#constr = " DSN=MS Access Database; DBQ={0};".format(UDC)
# DRIVER connection
constr = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:/USERS/DOCUMENTS/ACCESS;DBQ=C:/USERS/DOCUMENTS/ACCESS/UDC.accdb"

# Connect to database UDC and open cursor
db = pypyodbc.connect(constr)
cursor = db.cursor()

sql = "UPDATE * INTO [tblLogfile]" + \
      "FROM [Excel 8.0;HDR=YES;Database=C:/Users/Documents/Access/Excel.xls].[Tab$];"

cursor.execute(sql)
db.commit()

cursor.close()
db.close()

error

pypyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.')
SiHa
  • 7,830
  • 13
  • 34
  • 43
fili
  • 39
  • 1
  • 9
  • First decide what you want to do: https://stackoverflow.com/questions/2002500/what-are-differences-between-insert-and-update-in-mysql -- Then read up on the appropriate syntax. – Andre Jun 27 '17 at 08:46
  • @andre I have read about the UPDATE statement but I don´t understand how I should write it in my sgl variable, there is no examples on how to link up the excel file with access and that's what im trying to do. – fili Jun 27 '17 at 09:25
  • But then I don't know if doing it my way is the best way to do this transfer, so if you have some suggestions ill be glad to hear – fili Jun 27 '17 at 09:26
  • 1
    *Insert is for adding data to the table, update is for updating data that is already in the table.* -- You need an `INSERT` statement. – Andre Jun 27 '17 at 10:01

1 Answers1

0

You need SQL something like:

sql = "INSERT INTO [tblLogfile] (<list of field names>) SELECT <list of matching field names> FROM [Excel 8.0;HDR=YES;Database=C:/Users/Documents/Access/Excel.xls].[Tab$];"
Gustav
  • 53,498
  • 7
  • 29
  • 55