0

Im trying to perform an update query on a table that its on a separate database, so far i have this SQL:

UPDATE [;database=C:\QA_Daily_YTD_Report_Export.accdb].[YTD-Daily_Report] AS EXT_DB
SET EXT_DB.Category1 = "1"
WHERE (EXT_DB.Category1 = "status1");

When i run this it returns an "invalid operation" error. Any idea what im doing wrong?

Thomas G
  • 9,886
  • 7
  • 28
  • 41
Eduardo Rivera
  • 43
  • 2
  • 6
  • If Category1 is numeric you do not need quotes, but Access will accept it. Is your db really on the root ( c:\ ) ? – Fionnuala Sep 03 '14 at 00:01
  • Category1 is a text field, the database is not actually in my root directory(i just didnt want to add the whole path). – Eduardo Rivera Sep 03 '14 at 14:31
  • So i end up doing this in VBA and it works: Set db_external = OpenDatabase(CurrentProject.Path & "\QA_Daily_YTD_Report_Export.accdb") SQL = "UPDATE [YTD-Daily_Report]" & Chr(13) & _ "SET [YTD-Daily_Report].Category1 = 'Mensajería '" & Chr(13) & _ "WHERE ([YTD-Daily_Report].Category1= '4');" db_external.Execute SQL – Eduardo Rivera Sep 03 '14 at 18:52

3 Answers3

1

I would recommend linking the table [YTD-Daily_Report] into your database because you can easily put the update query into your code without having your code execute the connection to the other database.

You can link a table in Access by clicking on the External Data. Then click on the Access symbol. enter image description here

You should then get a dialog box like this: enter image description here

Be sure you choose the second radio button because you don't want to import the data from the database, just link it.

Navigate to the location of the Database and click on it. Then make sure your database is shown in the dialog box above and click okay. enter image description here

You should then get a dialog box like this one that will show the table you won't. Highlight it and click okay. Now you can rename the linked table with any name you want and this will be a much less of a stumbling block for your work.

Karlomanio
  • 344
  • 4
  • 11
0

Try to omit ;database=

 UPDATE [C:\QA_Daily_YTD_Report_Export.accdb].[YTD-Daily_Report] AS EXT_DB SET EXT_DB.Category1 = "1" WHERE (EXT_DB.Category1 = "status1");
4dmonster
  • 3,012
  • 1
  • 14
  • 24
  • no, no error in that part, im typing the exact path and file name. Im still getting the "invalid operation" error. The field "Category1" is defined as a textbox. – Eduardo Rivera Sep 03 '14 at 15:04
0

I ended up using VBA in a form, just in case someone is wondering how here it is:

Dim SQL As String
Dim db_external As Database
Set db_external = OpenDatabase(CurrentProject.Path & "\QA_Daily_YTD_Report_Export.accdb")
SQL = "UPDATE [YTD-Daily_Report]" & Chr(13) & _
"SET [YTD-Daily_Report].Category1 = '" & New_value & "'" & Chr(13) & _
"WHERE ([YTD-Daily_Report].Category1= '" & Look_up_value & "');"
db_external.Execute SQL
Eduardo Rivera
  • 43
  • 2
  • 6