tabledef, is this correct

[keving99]

Hi all
I need to write some code to add fields to a table in a remote database, i have come accross the tabledef command, but i do not want to create a new table and add data, which i cannot get around. This is the code i am using and i get error table already exists, which is correct, but how can i just add new fields.
any help very much apprechiated.

Private Sub Command1_Click()

Dim dbsLSG As Database
Dim tdfNew As TableDef
Dim fldLoop As Field
Dim prpLoop As Property
Dim strDataPath As String


strDataPath = "C:\LSG Enfield\LSG2000-Enfield-data.mdb"

Set dbsLSG = OpenDatabase(strDataPath)

Set tdfNew = dbsLSG.CreateTableDef("members")


' Create and append new Field objects for the new
' TableDef object.
With tdfNew
' The CreateField method will set a default Size
' for a new Field object if one is not specified.
.Fields.Append .CreateField("TextField", dbText)
.Fields.Append .CreateField("IntegerField", dbInteger)
.Fields.Append .CreateField("DateField", dbDate)
End With

dbsLSG.TableDefs.Append tdfNew

Debug.Print "Properties of new Fields in " & tdfNew.Name

' Enumerate Fields collection to show the properties of
' the new Field objects.
For Each fldLoop In tdfNew.Fields
Debug.Print " " & fldLoop.Name

For Each prpLoop In fldLoop.Properties
' Properties that are invalid in the context of
' TableDefs will trigger an error if an attempt
' is made to read their values.
On Error Resume Next
Debug.Print " " & prpLoop.Name & " - " & _
IIf(prpLoop = "", "[empty]", prpLoop)
On Error GoTo 0
Next prpLoop

Next fldLoop

' Delete new TableDef because this is a demonstration.
'dbsLSG.TableDefs.Delete tdfNew.Name
dbsLSG.Close


End Sub

 

[gemma-the-husky]

instead of creating a new table def

simply

set tdfnew = currentdb.tabledefs("existingtable")

will point to an existing table, which i think is what you want

 

[keving99]

instead of creating a new table def

simply

set tdfnew = currentdb.tabledefs("existingtable")

will point to an existing table, which i think is what you want

Hi, thanks for reply, the table is not in the current database its in a remote database, when i exicute i get error 'item not found in this collection' i also tried
Set tdfNew = strDataPath.TableDefs("members"), but it did not like it.
strDatapath being the full path to the remote databse.

 

[boblarson]

Well, why aren't you using your initial:
dbsLSG
instead of CurrentDb?

You should be able to substitute Gemma's CurrentDb with dbsLSG

 

[keving99]

Well, why aren't you using your initial:
dbsLSG
instead of CurrentDb?

You should be able to substitute Gemma's CurrentDb with dbsLSG

Nice one, almost there, i open the remote table and make sure the new fields are not added, close the remote, then run the code, i get an error ' cannot append. An object already exists in the collection.', when i open the remote table it has added the new fields, does this mean the loop is running through again?
Thanks

 

[keving99]

Nice one, almost there, i open the remote table and make sure the new fields are not added, close the remote, then run the code, i get an error ' cannot append. An object already exists in the collection.', when i open the remote table it has added the new fields, does this mean the loop is running through again?
Thanks

Got it, removed the 'dbsLSG.TableDefs.Append tdfNew, i assume the loop was doing the work, then this was trying to do it again.

Many thanks all.

 

[gemma-the-husky]

i did this for a while, but i got some errors adding certain field types. can't remember what they were exactly. things like checkboxes not being appended as proper checkbox types - so watch out for that

 

[keving99]

i did this for a while, but i got some errors adding certain field types. can't remember what they were exactly. things like checkboxes not being appended as proper checkbox types - so watch out for that

Thats good, all the fileds i need to add are yes/no fields, thanks for advise