copy table structure

[Loni - RWT]

I have a db that I'm continuing to develope. I wanted to update the working
copy with the new features. This version has some additional tables and some
additional fields in current tables. I manually copied and pasted, structure
only, all the tables to the working copy. The new tables are there but the
additional fields have not been added to the old tables. This is a split db.
I've tried to copy and paste several times, keyboard shortcut as well as
icon, but have been unsucessful.
Does copy table structure not add to existing tables? If so , how do I add
these fields short of manually setting up each one?
Thanks!

 

[Arvin Meyer [MVP]]

Not my code and I haven't tested it, but it looks like it should work fine:

Private Sub AddField(tbl As TableDef, strField As String, Optional strType
As String = "Text", _
Optional strDefault As String, Optional intSize As Integer = 50,
Optional blnZero As Boolean = False)
' Used to add fields to an existing table
' Accepts: tbl - TableDef for table to be amended
' strField - name of field to be created
' strType - data type for field to be created
' strDefault - string containing the Default Value. This could be a
formula
' intSize - integer of the size of the field
' blnZero - is field to allow zero length string

Dim fld As Field
Dim intType As Integer ' holds type of field, THis is an integer value
internally

'What type of field are we adding
Select Case strType
Case "YesNo"
intType = dbBoolean
Case "Byte"
intType = dbByte
Case "Currency"
intType = dbCurrency
Case "DateTime"
intType = dbDate
Case "Double"
intType = dbDouble
Case "Integer"
intType = dbInteger
Case "Long"
intType = dbLong
Case "Memo"
intType = dbMemo
Case "Single"
intType = dbSingle
Case "Text"
intType = dbText
End Select

'add the field
Set fld = tbl.CreateField(strField)
' Set field properties.
fld.Type = intType
If intType = dbText Then
fld.Size = intSize
fld.AllowZeroLength = blnZero
End If
If strDefault <> "" Then
fld.Properties("DefaultValue") = strDefault
End If
' Append fld to Fields collection.
tbl.Fields.Append fld

End Sub

 

[Peter Hibbs]

Provided you have split the database into Front-End and Back-End and
you want to add tables and/or fields to the back end then the code at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE Update Utility.mdb'

will do this automatically for you. If you haven't split the database
then you should.

HTH

Peter Hibbs.

 

[Loni - RWT]

Thanks for your help. I'll give it a try when I get the chance.

 

[Loni - RWT]

I wanted to look at this code but when I go to the link I get an error
message " arguments are of the wrong type". I tried to find the page but was
unable to find anything that looked right to me.
Thanks for your help!

 

[Peter Hibbs]

Loni.

Try http://www.rogersaccesslibrary.com/index.htm

click on Other Developer Libraries and then find my name in the list.
The first two links are what you want. If that doesn't work then post
back and I can send you the files direct.

HTH

Peter Hibbs.

 

[Loni - RWT]

Thanks a bunch! At a quick glance it looks like it's all self explanitory.
If there are any further questions I'll post back.

 

[Loni - RWT]

Since I've already added the needed fields to my developement back end, how
do I start with your update utility? I added the new fields in the
developement ubeForm but when I run the update I get an error message that
the field already exists. I understand why this is but how do I get started?

Thanks

 

[Peter Hibbs]

Loni,

As I understand it you have your development database in which you
have already added one or more extra fields to one or more tables and
you have another user database on a different PC which does not yet
have the extra fields added to the back-end file. You have added the
UBE code to your development front-end file and added the extra fields
to the UBE table but when you try to run the update it gives an error
and exits (which, as you say, it will).

How many fields are you talking about here? Are you only talking about
extra fields or have you made other changes? I presume that you have
already added the ubeVersion field to a table in the back-end file of
both databases.

It seems to me that you have three options :-
1. Add the extra fields to the user's back-end file manually and
then just use the UBE system to add any further fields in the future.

2. Delete the extra fields (the ones that are not in the users
back-end file) from your development back-end manually and then add
them again using the UBE system.

3. Modify the UBE code as shown below. This option will allow the
update code to ignore any errors generated by the system. However, you
would need to be careful if you do this as it may be possible for the
development database and user's database to get out of synch with
unpredictable results.

To modify the UBE code, find the UpdateBackEndFile function in the
ubeUpdateCode module. Replace the two lines of code after the
ErrorCode: label with the code below.


DoCmd.Close acForm, "ubeUpdating" If MsgBox(Err.Description &
" (Reference No = " & vID & "), " _
& "do you want to ignore this error and continue?", vbQuestion _
+ vbYesNo, "Update Error") = vbNo Then Exit Function
Resume Next

You should be able to copy and paste it into the code module.

Before you run it make a copy of the front-end and back-end files of
your development database and also make back up copies of the user's
files before they run the updated front-end, (just in case). Of
course, the user should not get any error messages because their
version will not already have the extra fields.

I have not had a chance to test this modification extensively so there
are no guarantees that there will be no problems.

Please let me know how you get on.

Peter Hibbs.

 

[Loni - RWT]

Peter,
Thanks for your prompt reply. Your understanding is correct except that
both DB's are on the same PC. I had thought, after posting, that maybe I
should just manually update the working copy to get them in sync for now. I
have printed table defs for both so this shouldn't be a big chore. Any
future changes can be made through the utility and then the updating will be
easy. I am continually amazed and grateful for all the help that is
available here!
Many thanks again!

 

[Peter Hibbs]

Loni,

Glad you are OK now. Just one thing I would add (although you are
probably already aware) is that you need to be very careful with
having two FE-BE systems on the same PC. It is very easy when swapping
files around to have the front-end file pointing at the wrong back-end
file and not realise it. Just thought I would mention it.

Good luck with your project.

Peter Hibbs.