Setting default value in a column (1 Viewer)

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
Using a DoCmd.RunSQL to create a column in a table, now I need to set the default value and I'm unsure how to do that. Is it in the syntax? What I've found thus far hasn't worked for me.

Code:
DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [FileType] Text;"

Thanks in advance,
 

rainman89

I cant find the any key..
Local time
Today, 09:42
Joined
Feb 12, 2007
Messages
3,016
It would be something like

Code:
[SIZE=2]ALTER TABLE tblSears ADD fileType text NOT NULL CONSTRAINT [DF_tblsears_fileType] DEFAULT ('Some Value')[/SIZE]
OR after you have created the table
Code:
ALTER TABLE tblSears ALTER COLUMN fileType SET DEFAULT 'someType'
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
I'll give this a shot and let you know, in the middle of running the reports manually atm. =)
 

DCrake

Remembered
Local time
Today, 14:42
Joined
Jun 8, 2005
Messages
8,634
Why are you trying to add a field (column) to an existing table?

David
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
The inventory reporting I do has about 16 columns in an Excel template. Of which I only populate about 6 fields from data I have others contain info such as date, filetype ("IN" for inventory), etc. So I'm importing the data in, running a make table query and adding columns with the default values set so I can export/email to the customer.
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
BTW I'm a complete access noob, I tinkered with it years ago. I'm probably doing it all wrong but its working . . . thus far =D
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
The above listed code errors out.
Code:
DoCmd.RunSQL "ALTER TABLE tblSears ADD FileType Text NOT NULL CONSTRAINT [DF)tblSears_FileType] DEFAULT ('IN');"
This code runs but doesn't even add the FileType field.

I'll try modifying the field after it has already been added.
 

rainman89

I cant find the any key..
Local time
Today, 09:42
Joined
Feb 12, 2007
Messages
3,016
The above listed code errors out.
Code:
DoCmd.RunSQL "ALTER TABLE tblSears ADD FileType Text NOT NULL CONSTRAINT [DF[COLOR=Red])[/COLOR]tblSears_FileType] DEFAULT ('IN');"
This code runs but doesn't even add the FileType field.

I'll try modifying the field after it has already been added.

Hope that extra ) is a typo
 

DCrake

Remembered
Local time
Today, 14:42
Joined
Jun 8, 2005
Messages
8,634
Seems to me that the concept is correct but the logic is wrong. If you import all the columns from spreadsheet, or even easier simply link the spreadsheet and have seperate queries that bring in the desired columns/fields depending on the criteria.

David
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
O.k. it did run, here's the problem. I need it to update for each row already in the table, not just new entries. Let me post the whole code and let you see what I'm doing.
 

rainman89

I cant find the any key..
Local time
Today, 09:42
Joined
Feb 12, 2007
Messages
3,016
Well in that case you need an update query also!!
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
My inventory spreadsheet has these columns:
ItemNumber, UPC, Type(discon/active), Size, Width, Price, Available

The output has:
FileType, VendorSKU, Available, QTY, NextShipQty, NextShipDate, Manufacturer, ManufacturerSKU, Description, UnitCost, UnitCost2, UnitCost3, UnitCost4, Deactivated, MerchDept,UoM, MerchantSKU, Merchant, GS1ID

I have a form that I want to use to import the data as well as write the reports.

INC code
Code:
Private Sub Import_Data_Click()
    
    'Error Handling
    On Error Resume Next
        
        'Table removal and recreation with data field changes
        DoCmd.Close acTable, "tblInventory"
        DoCmd.DeleteObject acTable, "tblInventory"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblInventory", "C:\Myfile.xls", True
        DoCmd.RunSQL "ALTER TABLE tblInventory ALTER COLUMN Size Double"
        DoCmd.RunSQL "ALTER TABLE tblInventory ALTER COLUMN Available Integer"
        
        'Toggles Query Warnings and changes negative to 0
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tblInventory SET [Available]=0 WHERE [Available]<0"
        DoCmd.SetWarnings True
                
End Sub

This is my import command button It locates the file, brings it in, changes a few column data types and updates all negative numbers to "0"

The next part is the output table, I have a query written to pull styles "XYZ" where size and width = "Whatever".

Report comand button is where I'm currently working.
Code:
Private Sub Sears_Report_Click()
    'Error Handling
    On Error Resume Next
        
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qrySears", acViewNormal, acEdit
        DoCmd.SetWarnings True
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [FileType] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [InStock] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [NextShipQty] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [NextShipDate] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [Manufacturer] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [ManufacturerSKU] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [UnitCost2] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [UnitCost3] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [UnitCost4] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [MerchDept] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [UoM] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [Merchant] Text;"
        DoCmd.RunSQL "ALTER TABLE tblSears ADD COLUMN [GS1_ID] Text;"
        
        
End Sub

FileType should be "IN" for all 314 records returned. InStock Yes if >0, No if =0 in "Available" for the UPC(linked via relationship). UnitCost is pulled from a seperate table for this customer's pricing. UoM is EA for each record returned.

Now this is the part where you drop your face into the palm of your hand and groan. >.<

I've probably taken off way more than I can chew but it's fun trying to figure it out.
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
Also, I am aware of the fact I'm removing and replacing my data daily. We run a db here but I'm not allowed access to it. I just get a spreadsheet emailed to me daily so I figured I'd start fresh each day.
 

rainman89

I cant find the any key..
Local time
Today, 09:42
Joined
Feb 12, 2007
Messages
3,016
You are doing all the table creation and modifying in SQL instead of creating the table in access and just importing the spreadsheets????

Why??
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
Lol, again I'm very new at this and my biggest resource is the web. Most of this is snippets of code I've found that I've managed to get to work for me.
 

rainman89

I cant find the any key..
Local time
Today, 09:42
Joined
Feb 12, 2007
Messages
3,016
Ok... your best option would be to create the table as you would like it.. with all columns included then import your spreadsheet....

One note though, when you go from excel to access a MAJOR, let me stress that, help to you would be to read up on normalization....

If you don't listen to anything else I say, listen to that.
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
So if I take what I have and scrap it, replace it with the following code, how would I then go about setting default values?
Code:
Public Sub Create_tblSears()
    Dim db As Database, tbl As TableDef, fld As Field
        Set db = CurrentDb()
        Set tbl = db.CreateTableDef("tblSears")
            Set fld = tbl.CreateField("UPC Number", dbText)
            fld.OrdinalPosition = 1
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("InStock", dbBoolean)
            fld.OrdinalPosition = 2
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("Quantity", dbInteger)
            fld.OrdinalPosition = 3
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("NextShipQty", dbText)
            fld.OrdinalPosition = 4
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("NextShipDate", dbText)
            fld.OrdinalPosition = 5
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("Manufacturer", dbText)
            fld.OrdinalPosition = 6
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("ManufacturerSKU", dbText)
            fld.OrdinalPosition = 7
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("Description", dbText)
            fld.OrdinalPosition = 8
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("UnitCost", dbCurrency)
            fld.OrdinalPosition = 9
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("UnitCost2", dbCurrency)
            fld.OrdinalPosition = 10
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("UnitCost3", dbCurrency)
            fld.OrdinalPosition = 11
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("UnitCost4", dbCurrency)
            fld.OrdinalPosition = 12
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("Deactivated", dbText)
            fld.OrdinalPosition = 13
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("MerchDept", dbText)
            fld.OrdinalPosition = 14
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("UoM", dbText)
            fld.OrdinalPosition = 15
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("MerchantSKU", dbText)
            fld.OrdinalPosition = 16
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("Merchant", dbText)
            fld.OrdinalPosition = 17
            tbl.Fields.Append fld
            Set fld = tbl.CreateField("GS1 ID", dbText)
            fld.OrdinalPosition = 18
            tbl.Fields.Append fld   
        db.TableDefs.Append tbl
RefreshDatabaseWindow
End Sub
 

rainman89

I cant find the any key..
Local time
Today, 09:42
Joined
Feb 12, 2007
Messages
3,016
You dont have to scrap it. If you want to go purely the code way then your first way will work.

I am just wondering why you are trying to do this all with code....
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
Automation. Or maybe I'm not following what you mean. I can do all of this in Excel manually which takes several hours or I can experiment with some code and have a one-click solution.
 

crownedzero

Registered User.
Local time
Today, 08:42
Joined
Jun 16, 2009
Messages
54
Or are you talking about just setting up all of my tables and then removing/adding/updating data?
 

Users who are viewing this thread

Top Bottom