Mavreich
asked on
Updating a backend
Hi all,
My question is... How do you manipulate backend tables/fields from the linked frontend.
In particular I would like to be able to (from code in the front end when a certain form is opened) add new fields to tables, modify relationships, redefine fields, create and delete fields or tables all from the front end.
Can this be done. Examples would be most welcome.
Thanking you all in advance...
Mavreich
My question is... How do you manipulate backend tables/fields from the linked frontend.
In particular I would like to be able to (from code in the front end when a certain form is opened) add new fields to tables, modify relationships, redefine fields, create and delete fields or tables all from the front end.
Can this be done. Examples would be most welcome.
Thanking you all in advance...
Mavreich
you can do it without creating a workspace as well,
just by adding the database you want to manipulate
for example
database at c:\test
table : mytable
new column : mynewcolumn
ALTER TABLE [c:\test].[mytable] ADD COLUMN mynewcolumn Text(50)
best regards,
yp.
just by adding the database you want to manipulate
for example
database at c:\test
table : mytable
new column : mynewcolumn
ALTER TABLE [c:\test].[mytable] ADD COLUMN mynewcolumn Text(50)
best regards,
yp.
Mavreich,
As YP indicated, you can do a lot with SQL. Of course there is always DAO methods as well. Mostly it depends on the version your using. It was only from A2000 and up that the SQL support for DML operations was added.
The on-line help is full of examples. Here is one:
Sub AlterTableX1()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.md b")
' Add the Salary field to the Employees table
' and make it a Currency data type.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary CURRENCY;"
dbs.Close
End Sub
This example removes the Salary field from the Employees table.
Sub AlterTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.md b")
' Delete the Salary field from the Employees table.
dbs.Execute "ALTER TABLE Employees " _
& "DROP COLUMN Salary;"
dbs.Close
End Sub
The MSKB (Microsoft Knowledge base) is also a great source for examples. Goto support.Microsoft.com. For example, here's code from one article that shows how to add a Autonumber field via DAO:
'************************* ********** ********** ********** ********
' FUNCTION: AddCounter()
'
' PURPOSE: Programmatically adds an AutoNumber field to an
' existing table.
'
' ARGUMENTS:
'
' TName: The name of the table.
' FName: The name of the new AutoNumber field.
'
' RETURNS: True (error was encountered) or
' False (no error) as an integer.
'
'************************* ********** ********** ********** ********
Function AddCounter (TName As String, FName As String) As Integer
Dim DB As Database, TDef As TableDef, Fld As Field
' Get the current database.
Set DB = CurrentDb()
' Open the tabledef to which the counter field will be added.
Set TDef = DB.TableDefs(TName)
' Create a new AutoNumber field of type LONG
' with the automatic increment attribute.
Set Fld = TDef.CreateField(FName, dbLong)
Fld.Attributes = dbAutoIncrField
' If you are using version 2.0, replace the
' two lines above with the following two lines.
' Set Fld = TDef.CreateField(FName, DB_LONG)
' Fld.Attributes = DB_AUTOINCRFIELD
' Trap for any errors.
On Error Resume Next
' Append the new field to the tabledef.
TDef.fields.Append Fld
' Check to see if an error occurred.
If Err Then
AddCounter = False
Else
AddCounter = True
End If
DB.Close
End Function
Anything else you need?
Jim.
As YP indicated, you can do a lot with SQL. Of course there is always DAO methods as well. Mostly it depends on the version your using. It was only from A2000 and up that the SQL support for DML operations was added.
The on-line help is full of examples. Here is one:
Sub AlterTableX1()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.md
' Add the Salary field to the Employees table
' and make it a Currency data type.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary CURRENCY;"
dbs.Close
End Sub
This example removes the Salary field from the Employees table.
Sub AlterTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.md
' Delete the Salary field from the Employees table.
dbs.Execute "ALTER TABLE Employees " _
& "DROP COLUMN Salary;"
dbs.Close
End Sub
The MSKB (Microsoft Knowledge base) is also a great source for examples. Goto support.Microsoft.com. For example, here's code from one article that shows how to add a Autonumber field via DAO:
'*************************
' FUNCTION: AddCounter()
'
' PURPOSE: Programmatically adds an AutoNumber field to an
' existing table.
'
' ARGUMENTS:
'
' TName: The name of the table.
' FName: The name of the new AutoNumber field.
'
' RETURNS: True (error was encountered) or
' False (no error) as an integer.
'
'*************************
Function AddCounter (TName As String, FName As String) As Integer
Dim DB As Database, TDef As TableDef, Fld As Field
' Get the current database.
Set DB = CurrentDb()
' Open the tabledef to which the counter field will be added.
Set TDef = DB.TableDefs(TName)
' Create a new AutoNumber field of type LONG
' with the automatic increment attribute.
Set Fld = TDef.CreateField(FName, dbLong)
Fld.Attributes = dbAutoIncrField
' If you are using version 2.0, replace the
' two lines above with the following two lines.
' Set Fld = TDef.CreateField(FName, DB_LONG)
' Fld.Attributes = DB_AUTOINCRFIELD
' Trap for any errors.
On Error Resume Next
' Append the new field to the tabledef.
TDef.fields.Append Fld
' Check to see if an error occurred.
If Err Then
AddCounter = False
Else
AddCounter = True
End If
DB.Close
End Function
Anything else you need?
Jim.
ASKER
Hi JDettman and other experts,
Thankyou for you help. I have had a look at all your suggestions but I am still unable to get it to work.
JDettman your examples are the closet i have got but i am getting hung up on 1 error. I forgot to mention that the backend is protected by a password. I have tried every way i can think off to pass the password through when trying to alter the table. I keep getting an invalid password error. I know what the password is but can not see where i put it into the above example to get it to work.
Looking forward to your advice.
Regards
Mavreich
Thankyou for you help. I have had a look at all your suggestions but I am still unable to get it to work.
JDettman your examples are the closet i have got but i am getting hung up on 1 error. I forgot to mention that the backend is protected by a password. I have tried every way i can think off to pass the password through when trying to alter the table. I keep getting an invalid password error. I know what the password is but can not see where i put it into the above example to get it to work.
Looking forward to your advice.
Regards
Mavreich
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi JDettman,
This morning I am a very happy man. Thankyou very much for that example. Of all the places I tried putting the password that was not one of them. lol
A Thankyou to you all for your assistance. It is truly appreciated.
Regards
Mavreich
This morning I am a very happy man. Thankyou very much for that example. Of all the places I tried putting the password that was not one of them. lol
A Thankyou to you all for your assistance. It is truly appreciated.
Regards
Mavreich
Set myDBEngine = New PrivDBEngine
Dim wrkJet As Workspace
Dim DBServ As Database
myDBEngine.SystemDB = "C:\Test\Test.mdb"
Set wrkJet = myDBEngine.CreateWorkspace
Set DBServ = wrkJet.OpenDatabase(C:\Tes
Andy