Add a row to a table from code

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add a row to a table from code

Post by HansV »

That would look like this:

Code: Select all

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
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

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?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add a row to a table from code

Post by HansV »

If the path to the backend is the same on your PC and theirs, it should work,
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

Thanks Hans!! I should be good to go. I appreciate your help!
Leesha

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

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.

Thanks,
Leesha

Code: Select all

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add a row to a table from code

Post by HansV »

I am away from my computer at the moment. I will look at it tomorrow.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

Sounds good. Have a great day!

Gasman
StarLounger
Posts: 81
Joined: 22 Feb 2022, 09:04

Re: Add a row to a table from code

Post by Gasman »

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.
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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add a row to a table from code

Post by HansV »

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.
Best wishes,
Hans

Gasman
StarLounger
Posts: 81
Joined: 22 Feb 2022, 09:04

Re: Add a row to a table from code

Post by Gasman »

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.
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.

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

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

Code: Select all

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add a row to a table from code

Post by HansV »

As already pointed out, the lines

Code: Select all

    ' Path of the current database
    strPath = CurrentProject.Path
    If strPath <> "\" Then
        strPath = strPath & "\"
    End If
are superfluous since you overwrite strPath in the next part:

Code: Select all

    strPath = "C:\Trinity Solutions\TrinitySolutionsDatabaseTables\TrinitySolutions_be.accdb"
Which line causes the error?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

If I hit cancel after being prompted for the data source it points to
Set dbs = DBEngine.OpenDatabase(strFile)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add a row to a table from code

Post by HansV »

Ah, of course. Change strFile to strPath.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

You are too funny. It worked "of course".
Thanks and have a great day!
Leesha

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

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?

Thanks,
Leesha

Code: Select all

    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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add a row to a table from code

Post by HansV »

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.

Code: Select all

    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
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Add a row to a table from code

Post by Leesha »

Thanks Hans! I'm laughing because I tried "Auto", "Autonumber", "Autointeger".....would never have come up with "Autoincrement".
Leesha