By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Access Ugrade SQL

P: n/a
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..
Thanks

Mar 21 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
No, there isn't. You'd have to use VBA.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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..

Mar 21 '06 #2

P: n/a
Br
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
Mar 21 '06 #3

P: n/a

"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?

Mar 21 '06 #4

P: n/a

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

Mar 22 '06 #5

P: n/a
Yitzak wrote:
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.

< SNIP >


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.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When I said that you'd have to use VBA I meant you can create DDL
statements in QueryDefs and, using VBA, run each QueryDef in the
appropriate order.

Ex: Place the names of all the queries in a table. Open a Recordset
into the table & run each query:

Do While Not rs.Eof
DoCmd.OpenQuery rs!QueryName
Loop

By "No" I meant that you can't run an SQL script. You can place the DDL
statements in the QueryDefs, but only one DDL statement per QueryDef.

As posted by another, the advantage of using VBA is error traps (like
catching errors when a DDL statement tries to add a new column that
already exists).

VBA functions/subs can be run from the Debug window (ctrl-G from the
Database window) by invoking the function/sub's name. E.g.:

RunSQLBuilds

Will run the user created Sub "RunSQLBuilds." That routine should be
designed to run something like the While loop, above, to create the DB.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRCH89IechKqOuFEgEQLOtQCg7YkCJKVzwslhAj6EUh9jQa wdISIAoLWN
c989xJtdd/qLebMHgIX0/cQR
=46Z/
-----END PGP SIGNATURE-----
Mar 23 '06 #6

P: n/a

"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
Mar 23 '06 #7

P: n/a
Thanks you have written it for me.
The reason why I don't use the GUI - is its a live DB and some changes
take time - so they cannot work while I'm changing it. I work on my
own copy of DB test it then release it onto the Live DB file

Copying changes over manually - errors and time consuming. Ok I would
still need them out of system but with this code which I would save in
live DB - I can prepare little one of subs

call AddField("field1", text, 50)
call AddField("field2", boolean)

The key is I've tested it before so guaranteed to achieve Schema as I
wanted it. I could even get the support guy to run it.

Thanks for your help.

Mar 23 '06 #8

P: n/a

"Yitzak" <te*********@yahoo.co.uk> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...
Thanks you have written it for me.
The reason why I don't use the GUI - is its a live DB and some changes
take time - so they cannot work while I'm changing it. I work on my
own copy of DB test it then release it onto the Live DB file

Copying changes over manually - errors and time consuming. Ok I would
still need them out of system but with this code which I would save in
live DB - I can prepare little one of subs

call AddField("field1", text, 50)
call AddField("field2", boolean)

The key is I've tested it before so guaranteed to achieve Schema as I
wanted it. I could even get the support guy to run it.

Thanks for your help.

No problem.
The beauty of this sort of thing is that with proper vba coding you get much
more flexibilty than a single alter table statement, and you are only
limited by your imagination. For example, you could get the code to check
one database table against a copy known to have the latest schema. The
routines can fill in default data for new columns or add indexing.
Sure Access doesn't have all of the advantages of SQL Server, but the vba
coding could certainly check the file wasn't being used, rename it, take a
copy and work on it and if all goes well, rename to the original. Otherwise
you can roll back to the first version. This way you can ensure the changes
are either all applied 100% correctly or not applied at all.


Mar 23 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.