Private Sub cmdCreateLink_Click()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
' *** Substitute the path of the frontend database ***
Set dbs = DBEngine.OpenDatabase("path and filename of frontend")
' *** Replace NewTable with the name of the table that you want to link to ***
Set tdf = dbs.CreateTableDef(Name:="NewTable")
tdf.SourceTableName = "NewTable"
' This connects to the backend database
tdf.Connect = ";DATABASE=C:\Trinity Solutions\TrinitySolutionsDatabaseTables\Trinity Solutions.accdb"
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh
dbs.Close
End Sub
After getting some sleep I "think" I may have to way to do this. The front end update that would be installed after the new table is added would already contain the link and since the backend table now has the same table I have in demo, it should be good to go?
Hi Hans!
I'm working with the code you gave me to add a row to a table. I have a small db that has the cmdUpdate button on it. I changed the code from what you originally gave me to the code below. I have two issues:
1. When I run the code it opens a popup asking me to select a data source. When I ran the original code you gave me this didn't do this so I'm not sure what I need to do.
2. I need to format for the new row to be double integer.
Private Sub cmdUpdate_Click()
Dim dbs As DAO.Database
Dim strPath As String
Dim strFile As String
Dim strSQL As String
' Path of the current database
strPath = CurrentProject.Path
If strPath <> "\" Then
strPath = strPath & "\"
End If
' The other database to be updated
strPath = "C:\Trinity Solutions\TrinitySolutionsDatabaseTables"
' Open the other database
Set dbs = DBEngine.OpenDatabase(strFile)
' SQL to add field
strSQL = "ALTER TABLE tblIngredients ADD COLUMN PricePerCount YesNo"
' Execute the SQL
dbs.Execute strSQL, dbFailOnError
' Close the other database
dbs.Close
End Sub
PMFJI, but you are not setting your strfile, which should be "Trinity Solutions.accdb" ?
You have only defined the path?
So append the path and "\Trinity Solutions.accdb" to make strfile, then you should be OK?
Plus you are resetting your strPath?
Get in the habit of Debug.Print your variables to see what you actually have, not what you think you have.
Using Access 2007/2019. Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime. Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
What exactly do you mean by "double integer"?
If you want what Access calls Number (Long Integer), use Integer instead of YesNo.
If you want what Access calls Number (Double), use Double instead of YesNo.
PMFJIA
At the start of this thread, you said your BE was suffixed with _BE ?, yet you are now using what would appear a db without that suffix?
Using Access 2007/2019. Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime. Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Hi!
Yes I want an long integer set to double. I revised my code as below but am still prompted to select a data source. The table change goes into the backend table which are in C:\Trinity Solutions\TrinitySolutionsDatabaseTables. The frontend is call TrinitySolutions.accdb
Thanks!
Leesha
Private Sub cmdUpdate_Click()
Dim dbs As DAO.Database
Dim strPath As String
Dim strFile As String
Dim strSQL As String
' Path of the current database
strPath = CurrentProject.Path
If strPath <> "\" Then
strPath = strPath & "\"
End If
' The other database to be updated
strPath = "C:\Trinity Solutions\TrinitySolutionsDatabaseTables\TrinitySolutions_be.accdb"
' Open the other database
Set dbs = DBEngine.OpenDatabase(strFile)
' SQL to add field
strSQL = "ALTER TABLE tblIngredients ADD COLUMN PricePerCount Double"
' Execute the SQL
dbs.Execute strSQL, dbFailOnError
' Close the other database
dbs.Close
End Sub
Hi Hans!
I'm using the code below (which you gave me and I modified) to create a new table in another DB. It is working fine. My goal is to have the code NOT run IF the table already exists and to NOT give an alert that the table already exists. Ultimately, nothing would happen, including an alert, if the table exists. What would that look like? Also, with [ID] what would the code look like if this is an autonumber?
Dim dbs As DAO.Database
Dim strPath As String
Dim strFile As String
Dim strSQL As String
' Path of the current database
strPath = "C:\Trinity Solutions\TrinitySolutionsDatabaseTables"
If strPath <> "\" Then
strPath = strPath & "\"
End If
' The other database to be updated
strFile = strPath & "TrinitySolutions_BE.accdb"
' Open the other database
Set dbs = DBEngine.OpenDatabase(strFile)
' SQL to create table
strSQL = "CREATE TABLE tblLinks (ID INTEGER, ListName TEXT(255), ListAddress MEMO)"
' Execute the SQL
dbs.Execute strSQL, dbFailOnError
' SQL to create primary index
strSQL = "CREATE INDEX PrimaryKey ON tblLinks (ID) WITH PRIMARY"
' Execute the SQL
dbs.Execute strSQL, dbFailOnError
' Close the other database
dbs.Close
You can check whether a TableDef named tblLinks already exists, and only create a new table if not.
And to make ID an AutoNumber field instead of a Number field, change INTEGER to AUTOINCREMENT.
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strPath As String
Dim strFile As String
Dim strSQL As String
' Path of the current database
strPath = "C:\Trinity Solutions\TrinitySolutionsDatabaseTables"
If strPath <> "\" Then
strPath = strPath & "\"
End If
' The other database to be updated
strFile = strPath & "TrinitySolutions_BE.accdb"
' Open the other database
Set dbs = DBEngine.OpenDatabase(strFile)
' Check whether table exists
On Error Resume Next
Set tdf = dbs.TableDefs("tblLinks")
On Error GoTo 0
' Create table if it does not exist yet
If tdf Is Nothing Then
' SQL to create table
strSQL = "CREATE TABLE tblLinks (ID AUTOINCREMENT, ListName TEXT(255), ListAddress MEMO)"
' Execute the SQL
dbs.Execute strSQL, dbFailOnError
' SQL to create primary index
strSQL = "CREATE INDEX PrimaryKey ON tblLinks (ID) WITH PRIMARY"
' Execute the SQL
dbs.Execute strSQL, dbFailOnError
End If
' Close the other database
dbs.Close