Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding a table field definitions in another mdb via VB 1

Status
Not open for further replies.

WillemdeNie

Programmer
Sep 15, 2003
44
NL
I have to add some fields to the definition of a table which is situated in a linked table. I cannot open the other mdb because it is in the backend part of my application and that is of course on the users computer. I want to add some new functions in my front-end, which I then distribute to my users. The first thing to do when the user starts the application should be then adapting the definition of linked table in his own backend mdb. How could I do this?
Help will be appriciated!

Willem
 
Willem,

The easyiest way is to open a connection to that database and execute an SQL command (DDL commands) like

Dim cnn as ADODB.Connection
.
.
.
cnn.open
cnn.execute "ALTER TABLE tblCustomers ADD COLUMN Address TEXT(30)"

Take some time and explore this link, on the ALTER TABLE statement!
Post again if you need help on anything
 
Hi Jerry, I think you gave me the right answer but I have still a problem with it.
This is what I have created:
Public Function ConversieTabellen()
On Error GoTo Err_ConversieTabellen

Dim conDatabase As ADODB.Connection
Dim sDBName As String


'Initialize objects & variables.
Set conDatabase = Application.CurrentProject.Connection

sDBName = "C:\Program Files\Niebotel\NiebotelHaven.mdb"

conDatabase.Open
conDatabase.Execute "ALTER TABLE Ontvangsten ADD COLUMN Pinwaarde Currency"
conDatabase.Close

Exit_ConversieTabellen:
Exit Function

Err_ConversieTabellen:
MsgBox Err.Description
Resume Exit_ConversieTabellen

This code is included in my frontend and the table ontvangsten is a linked table in the backend

The result is: "Action not alowed when the object is opened"

Any idea?
 
WillemdeNie,

open a connection to that database meant the BE database where the table is physically located.

So this line is wrong
Set conDatabase = Application.CurrentProject.Connection
and should be something like

conDatabase. ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"
conDatabase.Open
 
Jerry thanks again, As you probably noticed I'm not that experienced. I hope you donot mind helpoing again. I tried what you wrote but run into new error.

This what I created:
Dim conDatabase As ADODB.Connection
Dim sDBName As String


conDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Niebotel\NiebotelHaven.mdb;User Id=Admin;Password=XXXXX;"
conDatabase.Open
conDatabase.Execute "ALTER TABLE Ontvangsten ADD COLUMN Pinwaarde Currency"
conDatabase.Close

Exit_ConversieTabellen:
Exit Function

Err_ConversieTabellen:
MsgBox Err.Description
Resume Exit_ConversieTabellen

End Function

On line: conDatabase.Execute it gives an error:
"Object variable or Blockvariable With is not assigned" or something like that because my messages are in Dutch.
I hope uou can help me (i'm already trying for hours!
Greetings Willem
 
Yes. You are right
Code:
Dim conDatabase As ADODB.Connection
Dim sDBName As String

Set conDatabase =New ADODB.Connection
If you dont have user-level security implemented in you database
Code:
conDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Niebotel\NiebotelHaven.mdb;User Id=Admin;Password=;"
else
Code:
conDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Niebotel\NiebotelHaven.mdb;User Id=Guess;Password=Who;Jet OLEDB:System Database=C:\Program Files\Niebotel\NiebotelHaven.mdw;" "
The rest as is...
 
Jerry, Great help!
Tanks again!!!
Willem (Netherlands)
 

Willem, glad to be of help. And thanx for the *

Gerassimos (Hellas)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top