Append Data Re-post

[Guest]

I am trying to append fields to a table in a database that already has
information. I was given the code below but I am having no luck with it.
It's a little to advanced for me, so can someone help me?

The excisting db is located in c:\cam\ and the tables are in the back-end
file called cam2000be.mdb. The forms and reports and such are in the same
directory and is called cam2000.mdb. The new files are the same name but
2005 instead of 2000. I put the following code in the forms code-window but
I do not know how to make the command button run the code. How do you tell
this code what db to append to and where it is located? And, will this make
sure the db is closed and the excisting database is the correct version (the
filename tells the version). Thanks

-------------------Start of code--------------------------
Dim db As DAO.Database 'Ticket Control Database handle
Dim dbname As String 'Ticket Control Pathfilename

'This opens and locks the database---------------------------------
Sub OpenAndLockDatabase()
Const EXCLUSIVEMODE As Boolean = True

'was file specified?
If (Nz(dbname, "") = "") Then
MsgBox "No database file name has been specified.", vbCritical
Exit Sub
End If

'does filename is valid or does file exist?
On Error Resume Next
If (Dir(dbname, vbArchive) = "") Then
MsgBox "The database file cannot be found.", vbCritical
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "The database file name is invalid.", vbCritical
Exit Sub
Exit Function

'open file exclusively
On Error Resume Next
Set db = DAO.OpenDatabase(dbname, EXCLUSIVEMODE)

If Err.Number <> 0 Then
Select Case Err.Number
Case 3045, 3196, 3006, 3356
MsgBox "The target database is in use " & _
"and cannot be locked.", vbCritical
Case 3049, 3343, 3182
MsgBox "The target file is damaged or is not " & _
"a valid database file.", vbCritical
Case 3051
MsgBox "The database cannot be locked. " & _
"It is either read-only or you need " & _
"access permission.", vbCritical
Case Else
Err.Raise Err.Number, Err.Source, Err.Description
End Select
Exit Sub
End If
End Sub

'This releases the database----------------------------------
Sub ReleaseDatabase()
db.Close
Set db = Nothing
End Sub

'This adds the "Inactive" field to the database------------------
Sub AddField()
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim fld1 As DAO.Field
Dim fld2 As DAO.Field
Dim prp1 As DAO.Property
Dim prp2 As DAO.Property

Set tdf1 = dbs.TableDefs("Employees")
Set tdf2 = dbs.TableDefs("Clients")

'Creates new field data type
Set fld1 = tdf.CreateField("Active", dbBoolean)
tdf.Fields.Append fld1
dbs.TableDefs.Refresh
Set fld1 = tdf.CreatField("Archive", dbBoolean)
tdf.Fields.Append fld2
dbs.TableDefs.Refresh

'Creates the checkbox attribute
Set prp1 = fld1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld1.Properties.Append prp1
dbs.TableDefs.Refresh
Set prp2 = fld2.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld2.Properties.Append prp2
dbs.TableDefs.Refresh

Set dbs = Nothing
End Sub


'This closes this update-program automatecaly----------------------------
Sub CloseOut()
DoCmd.Quit acQuitSaveNone
End Sub
-----------------End of code-------------------

 

[Guest]

You need to set the global string dbname to the database you want to
manipulate.

dbname = "C:\cam\..........cam2000be.mdb

Than call the functions OpenAndLockDatabase, AddField, ReleaseDatabase.

OpenAndLockDatabase
AddField
ReleaseDatabase