ALTER TABLE with Table Name Varible 2

[GooGoocluster]

This is the code I am using

CurrentDb.Execute "ALTER TABLE ('stDocName') ADD COLUMN Scanned YESNO"

I cant get it to recognize the varible

 

[jebry]

Hi!

Try it like this:

CurrentDb.Execute "ALTER TABLE (" & stDocName & ") ADD COLUMN Scanned YESNO"

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[GooGoocluster]

It returns a syntax error

 

[PHV]

And this ?
CurrentDb.Execute "ALTER TABLE " & stDocName & " ADD COLUMN Scanned YESNO"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GooGoocluster]

PH that worked Thank you!

I how would I make a default value to be "NO"
Also if this executes twice how would I elemibate duplicate fields?

i.e
If column exists do nothing.

Thanks again both of you

PHV I think you have answered all of my question on this board you are truly the man!

 

[GooGoocluster]

Actually I dont need the default Value to be "No" I need the Format to be "Yes\No"

 

[GooGoocluster]

This does not work but Am I on the right track

CurrentDb.Execute "ALTER TABLE " & stDocName & " ADD COLUMN Scanned YESNO"
Dim Scanned As Field
Set Scanned.DataFormat = Yes \ no

 

[GooGoocluster]

Here is another command just like the Add column but this one does not work it says bad syntax

Private Sub Command17_Click()
stDocName = Me.Combo13.Value
stFieldName = Me.Combo18.Value

CurrentDb.Execute "ALTER TABLE " & stDocName & " RENAME COLUMN " & stFieldName & " TO ScanID"
End Sub

 

[PHV]

Which SQL engine admits a RENAME COLUMN ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GooGoocluster]

this is the link. I have been trying to use as a reference

http://www.faqs.org/docs/ppbook/r22871.htm

 

[PHV]

Why not searching your local drives for files named jet*.chm ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GooGoocluster]

Fix the code

New Code

Private Sub Command17_Click()
stDocName = Me.Combo13.Value
stFieldName = Me.Combo18.Value
CurrentDb.TableDefs(stDocName).Fields(stFieldName).Name = "ScanID"
End Sub

I think this might help me with changing the Format Property

 

[GooGoocluster]

Sorry to keep bugging you guys.. any idea why it would give command would not execute query?

Private Sub Command8_Click()
stDocName = Me.Combo13.Value
CurrentDb.Execute "SELECT * FROM " & stDocName & " WHERE Scanned = Yes;"
End Sub

 

[PHV]

Because the Execute method is for action queries only, ie UPDATE, DELETE, ALTER, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GooGoocluster]

What would be the best way to do this:

I need to have the command button open a form datasheet or query. That is based on the table that the user selects form a combobox. Where condition is scanned = "Yes

 

[GooGoocluster]

Still having trouble this is the code I have so far. Its having troble with the FROM statement. Also when I type in just the table name the Select statment does not return all of the coulumns.

Here is the code so far.

Private Sub Command8_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim frm1 As Form
Set frm1 = Forms("frmScanned")
stDocName = "frmScanned"
stDocNameG = Me.Combo13.Value

DoCmd.OpenForm stDocName, , , stLinkCriteria
frm1.RecordSource = "SELECT * FROM '" & [stDocNameG] & "' WHERE Scanned = Yes"

End Sub

 

[lespaul]

try this:

frm1.RecordSource = "SELECT * FROM [" & stDocNameG & "] WHERE Scanned = Yes"


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

And instantiate frm1 AFTER the OpenForm call:
Private Sub Command8_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim frm1 As Form
stDocName = "frmScanned"
stDocNameG = Me!Combo13.Value
DoCmd.OpenForm stDocName, , , stLinkCriteria
Set frm1 = Forms(stDocName)
frm1.RecordSource = "SELECT * FROM [" & stDocNameG & "] WHERE Scanned = Yes"
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GooGoocluster]

Thanks to the both of you. Any Idea why it would return the record count but none of the columns?

 

[GooGoocluster]

I think I know there reason. The form I am opeing is a datasheet I had no Textboxes for the column names to appear. so nothing would show. Is there a way to display a datasheet with column names that are always differnt besides the column name Scanned?

The user imports there own table and they are not always set up in the same way.