"Yitzak" <te*********@yahoo.co.uk> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
Anthony England wrote: "Br@dley" <do***********@google.com> wrote in message
news:dv**********@news-02.connect.com.au... > MGFoster wrote:
>> No, there isn't. You'd have to use VBA.
>
> You can certainly use an SQL statement to get a list of objects
> (tables,
> forms, etc)... but don't think you can get the field names in
> tables.....
>
>
>> Yitzak wrote:
>>> Hi is there a way to run a SQL Script to Update an Access Schema.
>>> Without using VBA code.
>>>
>>> E.g. in mssql server I can run script like
>>>
>>> if not exists (select * from syscolumns where name = 'newfield')
>>> alter table mytable add column newfield varchar(10) NOT NULL
>>>
>>> go
>>>
>>> if not exists (select * from syscolumns where name = 'anotherfield')
>>> alter table mytable add column anotherfield varchar(8) NOT NULL
>>>
>>>
>>> anyhting similar in Access? Notice these scripts are rerunable - if
>>> column already exists it will not try and add it again..
>
> --
> regards,
>
> Br@dley
You could create a new query with the following:
alter table mytable add column newfield varchar(10) NOT NULL
Then save this as Query1.
If you double-click the query from the database window, a new field will
be
added, or an error message will be displayed (for example if the field
already exists).
This is called a Data Definition query, and is a way of using the Access
GUI
to add field without the use of VBA.
However, using VBA gives lots more flexibility, proper error-handling and
is
an integral part of it all. It's like asking "can we do this with SQL
Server without using T-SQL?" What's the point of the question?
I see what you are saying - the reason is its easier for me. I find
Access clunky (because I don't know it at all). So to work on one DB
file I would like to bring one Huge Query (SQL batch file) to the new
DB and run it to upgrade. Transfering it around as a text file.
I suppose I would have to make VBA functions like "addfield" which
checks for existance of field and adds it. Copy this over in a module
and invoke it on new DB. How do I invoke it?
Just in the project I'm working on the Access DB has no code in it -
just Data and Queries.
Thanks
I'm still not entirely sure I understand.
If you have Microsoft Access and wish to update a table by adding a field if
necessary, and this is a one-off upgrade process, then you do realise there
is no need to use code at all. I'm just pointing this out because you say
you don't know Access at all, and I'm suddenly wondering whether everyone is
providing coding examples, when you could be using Access's own GUI to do
it - which is extremely simple.
Well, let's assume you do need to do it via code. You could create a new
database with no tables in it, which simply has the function to update other
databases. This would have the advantage that you could set the code to
loop through hundreds of mdb files checking and upgrading if necessary.
After all, if you're not doing multiple files why would you do it in code?
Here is a function you could cut and paste into a new module which shows an
example for doing this:
Option Compare Database
Option Explicit
Public Const TABLE_NAME As String = "MyTableName"
Public Const FIELD_NAME As String = "MyNewFieldName"
'
Public Function AddField(strDbPath As String) As Long
' Tries to add the field
' Returns -2 if successful
' Returns -1 if field already existed
' Otherwise returns a number representing the error
On Error GoTo Err_Handler
Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim lngReturn As Long
Set wks = DBEngine.CreateWorkspace("Jet", "Admin", "", dbUseJet)
Set dbs = wks.OpenDatabase(strDbPath, True)
Set tdf = dbs.TableDefs(TABLE_NAME)
On Error Resume Next
Set fld = tdf.Fields(FIELD_NAME)
lngReturn = Err.Number
On Error GoTo Err_Handler
Select Case lngReturn
Case 0
' The field already exists
lngReturn = -1
GoTo Exit_Handler
Case 3265
' The field does not exist put the return value back
lngReturn = 0
Case Else
' Unexpected - so raise an error
Err.Raise lngReturn
End Select
Set fld = tdf.CreateField(FIELD_NAME, dbText, 50)
tdf.Fields.Append fld
lngReturn = -2
Exit_Handler:
AddField = lngReturn
If Not fld Is Nothing Then
Set fld = Nothing
End If
If Not tdf Is Nothing Then
Set tdf = Nothing
End If
If Not dbs Is Nothing Then
dbs.Close
Set dbs = Nothing
End If
If Not wks Is Nothing Then
Set wks = Nothing
End If
Exit Function
Err_Handler:
lngReturn = Err.Number
Resume Exit_Handler
End Function
