Inserting a column using vb

[TTR9000]

This is the code I have in Module1 which runs when the db is opened I would like to insert a column at the end of the Prosaim 852 Table before the table is opened. I also would like to name the new column Proto Number.

Here is the Code

Function AutoExec()

DoCmd.SetWarnings False

DoCmd.OpenQuery "proto mat", , acReadOnly
DoCmd.OpenQuery "Prosaim852 Query", , acReadOnly
DoCmd.OpenTable "Prosaim 852 Table", , acReadOnly

End Function

 

[mdmilner]

You'll end up using ALTER TABLE
You can take a peek at this in the VBA help.

But, are you sure you actually need to use this option?
If it's a field you will always need, you're better off not coding a change and just adding the field (column). If you're periodically adding new data types (such as rows with inventory item names and a new weeks inventory) - you're better off building a relational table (big topic - too long to explain)

If, on the other hand, you just want to display calculated values from existing table fields, just build a query that calculates the new value and then display the calculated value via the query.

Mike

 

[TTR9000]

I am making this for a co worker who has no idea how to use access I have made everything automatic for them except this feature Yes I would be adding the column every time. The column is there just so she can key in data. Any suggestions on what to do?

 

[mdmilner]

That explains why you're just opening three table/queries.
If he/she is going to key in information, maybe you shouldn't make it acReadOnly?

Think I need more information.

If I opened the mdb 10 times, would there be 10 more columns?
Or is this supposed to be a 'temporary' column that only exists during a login session?
Or is this column just supposed to be blank at the beginning of each login?

If #3 - how about adding this to your AutoExec before you open the table or queries based on it.

strSQL = "UPDATE tblName Set fldname = Null"
DoCmd.RunSQL strSQL

Mike

 

[TTR9000]

it is #3 and I put the code above where the command is the open Prosaim 852 Table and it says there is a error when the code is run the error is a
run time error 3144
syntax error in update statement

it says that the error is in this line of the code
DoCmd.RunSQL strSQL

 

[mdmilner]

Here's your code again with a variable declaration and brackets around the table & fieldnames. Anytime you have multi word names not connected by characters (such as underscores) you have to help Access understand what you're doing. That it's a table & field you're referencing.

A full way to do the field would be:
[Prosaim 852 Table].[Proto Number]

Also, what's the variable type for this extra field? The command to set the value to null/empty may need to be different depending on variable type. This will not create the field - it will need to exist already.

Function AutoExec() 
Dim strSQL As String

DoCmd.SetWarnings False 

strSQL = "UPDATE [Prosaim 852 Table] Set [Proto Number] = Null" 
DoCmd.RunSQL strSQL 

DoCmd.OpenQuery "proto mat", , acReadOnly 
DoCmd.OpenQuery "Prosaim852 Query", , acReadOnly 
DoCmd.OpenTable "Prosaim 852 Table", , acReadOnly 

End Function

 

[TTR9000]

I dont know what you mean by variable type. I need the field to be created everytime the code is run the field needs to be blank except for the name. Since I am using a append query to make that table it doesnt matter if the Proto Number field is put in there multiple times like you said in you first option because the append query deletes the table so it can create a new one. But the code didnt work, it ask me for a parameter value.

 

[mdmilner]

This is a variable declaration

Dim strSQL As String

Append queries add records to a table. They do not create tables.
Make-Table queries create tables from scratch (table gets re-created if it already exists). They look like:

SELECT fld1, fld2, fld3 INTO tblName
FROM tblSource

My code quote was for an UPDATE query which changes field values. It doesn't clear the table contents, it just clears a single field in all records.

Your program is saying you have a syntax problem in the SQL statement. I fixed two things - first is variable declarations and second was to specify precisely how to select the fieldnames.

How about this, I'll go down to basics.
Make sure your references are set: Open up any code module, then go up to the Tools Menu and select references. For this make sure that "Microsoft Access 9.0 Object Library" is checked (that's Access2K - Access97 would be 8.0) (it probably is by default)

Next, build your own delete query.
Go to the queries, use the wizard interface and select this pre-existing table with all fields including the one you wish to clear.

Only select the single field that you wish to clear the contents on.
In Design Mode go to the toolbar at the top of the screen and change it to an UPDATE query. In the 3rd Row labeled 'Update To' ONLY for the field you wish to clear, put the keyword 'Null'.

Now, save it.

Reopen it in design mode and then right click on the blue bar at the top of the query window. Select SQL View

Make sure that the SQL used in the AutoExec procedure looks precisely like the above. When you're assigning the command to the variable (strSQL) make sure you surround it with double quotes.

Try running it again.

Mike

 

[TTR9000]

Sorry I didnt mean append query I meant make table query so the Proto Number column isnt there to clear the value out of. This is what I want to happen I dont know if it can work or not. When the autoexec code is run when she opens the db I want the two queries to run and the for the table to open up with a blank proto number column in it. And that column will be deleted next time she runs that make table query because that overwrites that table right? I dont know if I am explaining this well, but thanks for all of the help you have given me this far.

 

[TTR9000]

Ok I got it to work here is the SQL statement that I used


SELECT Prosaim852.[EDI Type], Prosaim852.[Duns+4], Prosaim852.Payer, Prosaim852.[Sold-To], Prosaim852.UPC, Prosaim852.[Customer Number], Prosaim852.Proto_Number AS Proto_Number INTO [Prosaim 852 Table]
FROM Prosaim852
WHERE (((Prosaim852.Proto_Number) Is Null));


But now when the query is run it ask for a parameter and I just click ok and it runs and works fine. But how do I make it where that parameter box doesnt pop up?