Using SQL UPDATE command to update a field

[kwagner1]

Greetings - I'm new to using SQL with Access and i'm looking for some help with the following code.

- I want to update the "system" field in my table without any manual input on the UI. With the following code I am currently getting a pop-up msgbox (called "Enter Paramater Value") prompting me to enter the name of the value for the field. The "mynamehere" value is presented in the msgbox as what looks to be a label name for the input box.

My Code to update the "system" field is as follows:
DoCmd.SetWarnings False ' disables prompts
DoCmd.RunSQL "UPDATE [ML TF] SET [ML TF].SYSTEM = mynamehere"
DoCmd.SetWarnings True ' enable prompts



Thanks for your help!

 

[Norie]

What message box are you referring to?

Can we see the rest of the code?

Perhaps this is what you want.

DoCmd.RunSQL "UPDATE [ML TF] SET [ML TF].SYSTEM = " & mynamehere

 

[kwagner1]

that is all of the code. The "mynamehere" is the actual string value i want in the field (not a variable)

the msgbox (input box) from Access is titled: "Enter Parameter Value" - when i enter a value in the input box it updates my field in the table. I want the value to be populated without any UI interface.
[/img]

 

[Norie]

Well how do you actually want to populate it?

Where are you going to get a value for it?

 

[kwagner1]

i was hoping to set the value via a simple SQL command. I'm a newbie to SQL/Access... and was looking for a simple approach.

 

[kwagner1]

really what i'm trying to do is automate the following:

- copy my tables to new names
- delete all records in the existing tables
- add/update the 1st record in the table via the SQL command i'm having problems with (this is in order to import a new csv file... someone mentioned to me that in order to import in Access the tables must have 1 record in them.)

so far, i've got the 1st two steps automated... problems with the last piece about reinitializing 1 record in each of my tables...

 

[Norie]

someone mentioned to me that in order to import in Access the tables must have 1 record in them

That's nonsense. Who told you that?

What other code do you have already?

What I'm trying to work out is what value of mynamehere you want? Is it a table name, a date, a sequential number, some code you use elsewhere?

By the way what do you mean by 'copying to new names'?
Couldn't you just rename fields and tables?

 

[kwagner1]

if import comment is nonsense i'll look into it further here.... maybe that part of what i'm trying to do is not needed after all..... here is my entire code for what i'm doing: (simple DoCmd's to set up my 3 tables)
- i receive a new CSV file each week that replaces the entire contents of my 3 tables.


Sub SetupTables()
' created to set up new tables.

' delete the existing "Prior" tables
DoCmd.DeleteObject acTable, "Prior Merger"
DoCmd.DeleteObject acTable, "Prior Defects"
DoCmd.DeleteObject acTable, "Prior New Master"

' create new "Prior" tables with a copy of the existing tables
DoCmd.CopyObject , "Prior Merger", acTable, "Merger"
DoCmd.CopyObject , "Prior Defects", acTable, "Defects"
DoCmd.CopyObject , "Prior New Master", acTable, "New Master"


' Empty the existing tables to ready them for "import" process.
' Disable the prompts and re-enable when done
DoCmd.SetWarnings False ' disables the prompts
DoCmd.RunSQL "DELETE * FROM [Merger]"
DoCmd.RunSQL "DELETE * FROM [Defects]"
DoCmd.RunSQL "DELETE * FROM [New Master]"
DoCmd.SetWarnings True ' enables the prompts

' === this part is probably no longer needed ===
' Now populate first record of the tables with a value in order to import
DoCmd.SetWarnings False ' disables the prompts
DoCmd.RunSQL "UPDATE [Merger] SET [Merger].STATE = NewJersey"
DoCmd.RunSQL "UPDATE [Defects] SET [Defects].STATE = NewJersey"
DoCmd.RunSQL "UPDATE [Master] SET [Master].STATE = NewJersey"
DoCmd.SetWarnings True ' disables the prompts


End Sub

 

[Norie]

You still haven't said which value you want to update and how it's determined?

By the way why are you deleting prior data?

Why not just import the data to the existing table(s) and add a field to indicate the week it came from?

This could be updated easily using an UPDATE query.

This would give you the advantage of looking at and analysing historical data.

 

[kwagner1]

i think i like your idea of just importing into the existing table...(although my database is pretty big and i'm not sure if i want to expand its size (currently 117 MB) i will give that some thought.

in the meantime i'm investigating the import using "DoCmd.TransferSpreadsheet". i'm a little new to Access and was originally looking for a way to re-initialize the table with 1 record. Looks like thats not needed after all....