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

Modifying database schema from a vb program

P: n/a
This is sort of a newbie question.

I have an access database application. It is split into a backend data
file and GUI. I need to make changes to the db schema. The production
version of the data file is not accessible to me remotely. Rather than
make changes at the client location the best idea seems to be to write
a program to do this programaticaly. Just a small exe that the client
can download and run which will make the modifications. IS this the
best way to go? Can it be done easily in VB?

Aug 11 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"Welie" <we******@gmail.comschreef in bericht news:11*********************@b28g2000cwb.googlegro ups.com...
This is sort of a newbie question.

I have an access database application. It is split into a backend data
file and GUI. I need to make changes to the db schema. The production
version of the data file is not accessible to me remotely. Rather than
make changes at the client location the best idea seems to be to write
a program to do this programaticaly. Just a small exe that the client
can download and run which will make the modifications. IS this the
best way to go? Can it be done easily in VB?
Can definitively be done, but IMO this is not newbie stuff.

Why would you send a separate exe if you can do the same from your frontend?
Send them a new frontend which contains update-code.
In your update-code check if the update is still needed. (Check version of backend)
But as I told you: this is not newbie stuff.

Arno R

Aug 11 '06 #2

P: n/a
Yes, I do this all the time....

In fact, when I send clients a new front end (actually, they update it via
the following process).

http://www.kallal.ca/ridestutorialp/upgrade.html

Anyway, all the above does is un-packs (un-zips) a new front end
to the users machine. When the user launches the new front end, it
first re-links using the clients path name to the back end..

Next, my start-up code then runs updates to tables etc.

There is typical several operations that are quite common

1) - adding a new field to a existing table
2) - increasing the length of a existing fields
3) - adding a whole new table to the back end...
#1 is quite easy. Here is what my code will look like:

' add default fields

' check table defaults..and add default Tour Type field...

Set rst = CurrentDb.OpenRecordset("tblDefaults")
On Error GoTo AddDefaultTourType
Temp = rst!DefaultTourType.Name

rst.Close
Set rst = Nothing
Exit Sub
AddDefaultTourType:

rst.Close
GoSub AddDefaultTourTypes

Resume Next

AddDefaultTourTypes:

strToDB = strBackEnd

Set db = OpenDatabase(strToDB)

Set nT = db.TableDefs("tblDefaults")
nT.Fields.Append nT.CreateField("DefaultTourType", dbLong)
nT.Fields.Refresh
db.Close
Set db = Nothing

Set rst = CurrentDb.OpenRecordset("tblDefaults")
Return
End Sub

So, the above gives you an idea.....

So, my upgrade code actually runs every time on startup. This ensures that
if they client grabs a older data file (say from a backup), version, then
the upgrade code always runs...

Here is an example to upgrade the size of a field

' check size of Anotes in tblGroupRemind

Set db = CurrentDb
Set dbTable = db.TableDefs("tblGroupRemind")
Dim intSize As Integer
intSize = dbTable("ANotes").Size

Set dbTable = Nothing

If intSize = 50 Then

Dim nField As DAO.Field

Set db = OpenDatabase(strBackEnd)

db.Execute "ALTER TABLE tblGroupRemind ALTER COLUMN Anotes text(255)",
dbFailOnError

End If

db.Close

Now, to add a new table, I actually simply include the table in the front
end, but with a "C" appended to the end.
I also have the link already made in the front end...so, I do NOT have to
add this link via code...since it already
exist!! So, adding a whole new table to the back end is not hard if you take
this approach....

strFromDB = CurrentProject.FullName
strToDB = strBackEnd

DoCmd.TransferDatabase acExport, "Microsoft Access", strToDB, acTable,
"tblRemindDefaultsC", "tblRemindDefaults", False

Set db = CurrentDb
strLink = ";DATABASE=" & strBackEnd
Set dbTable = db.TableDefs("tblRemindDefaults")
dbTable.Connect = strLink
dbTable.RefreshLink

In fact, you can see it is only 3 lines of code to add the whole table to
the back end (since I just copy it from the front end). By the way, that
table is never deleted in the front end..but, just stays there.....you never
know when the client might open a older data file....

the next few lines are needed, since the re-link code in start-up would have
FAILED for the links in the front end..since NO table in the back end
existed...So, after you add the table, you must re-fresh the table link.
However, since the table link IS PROVIDED in my new front end...at least I
don't have to write code. So, having a table, and the link already done in
the front end saves a LOT of coding to create a new table....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 11 '06 #3

P: n/a
Albert D. Kallal wrote:
Yes, I do this all the time....
SNIP
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Albert, thank you so much for that detailed response. It's like a mini
tutorial and a quick read looks like it teaches me much of what I need
to know. A very good educational piece! Later today I'll start coding.

I guess it's not a newbie question. I should give myself more credit,
though I still feel very new to Access/DAO api.

-EW

Aug 13 '06 #4

P: n/a

Albert D. Kallal wrote:
Set db = CurrentDb
strLink = ";DATABASE=" & strBackEnd
Set dbTable = db.TableDefs("tblRemindDefaults")
dbTable.Connect = strLink
dbTable.RefreshLink

I have been following Alberts example above. The assumption is that the
linked table exists in the current database, but the link needs to be
refreshed since the underlying table may not have existed in the back
end database. In the code previous to this, I created the table in the
back end. I get to the point of setting the Connect property
(dbTable.Connect = strLink) but I get an error that the object no
longer exists or is invalid.

There is actually one thing that I am doing different than the above
code (but I am fairly certain I made the change only b/c I was having
this problem and was attempting a work around). The change is:

Set dbTable = CurrentDb.TableDefs("tblRemindDefaults")
Any ideas why this would be happening?

Finally though, it seems that even without reliniking the table, the
link works anyhow, even thought the table did not exists when the db
started up.

Thanks,
Elie

Aug 21 '06 #5

P: n/a
On 21 Aug 2006 03:18:26 -0700, "Welie" <we******@gmail.comwrote:

If the table is new in the BE, add a line:
dbTable.Tabledefs.Refresh
before you try to link to it.

-Tom.

>
Albert D. Kallal wrote:
> Set db = CurrentDb
strLink = ";DATABASE=" & strBackEnd
Set dbTable = db.TableDefs("tblRemindDefaults")
dbTable.Connect = strLink
dbTable.RefreshLink


I have been following Alberts example above. The assumption is that the
linked table exists in the current database, but the link needs to be
refreshed since the underlying table may not have existed in the back
end database. In the code previous to this, I created the table in the
back end. I get to the point of setting the Connect property
(dbTable.Connect = strLink) but I get an error that the object no
longer exists or is invalid.

There is actually one thing that I am doing different than the above
code (but I am fairly certain I made the change only b/c I was having
this problem and was attempting a work around). The change is:

Set dbTable = CurrentDb.TableDefs("tblRemindDefaults")
Any ideas why this would be happening?

Finally though, it seems that even without reliniking the table, the
link works anyhow, even thought the table did not exists when the db
started up.

Thanks,
Elie
Aug 21 '06 #6

P: n/a

Albert D. Kallal wrote:
Yes, I do this all the time....

In fact, when I send clients a new front end (actually, they update it via
the following process).

Set db = CurrentDb
strLink = ";DATABASE=" & strBackEnd
Set dbTable = db.TableDefs("tblRemindDefaults")
dbTable.Connect = strLink
dbTable.RefreshLink

Albert,

After I do the set db = currentDB, I can no longer make changes to vba
code modules. I get an error that "you do not have exclusive access to
the database at this time".

I have seen many postings about this but nothing that solves my
problems. Please Help!

Aug 21 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.