Add a row to a table from code

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

Add a row to a table from code

Post by Leesha »

Hi!
Is there a way to add a row to an existing table from another Access Database" I need to add "TurnOffPrintPopup" in tblAdministrativeInfo" to about 30 databases around the country and doing them individually myself will be an issue, even remoting in.
Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78241
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 »

How would the code connect to the databases?
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 »

The databases are all stored in the save place on their PC. I was thinking that the database running to code to add the row would be connected to tblAdministrativeInfo via a link? I'm sure you have a better idea :-)

User avatar
HansV
Administrator
Posts: 78241
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'm confused. If the database is stored on the user's PC, how would code running on your PC be able to update a table in the user's PC? :scratch:
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 »

My thought was to send them a database that has form with a command button that has the code to insert the row. Remember who you're talking to Hans! I've learned 80% of what I know from you lol. I have not idea how this would work, but this was my thought. How would you do it?

User avatar
HansV
Administrator
Posts: 78241
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 »

Will the database that you want to update always have the same name?
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 »

Yes. The database will always be called TrinitySolutions. The backend tables are called TrinitySolutions_BE

User avatar
HansV
Administrator
Posts: 78241
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 »

Let's say you name the command button cmdUpdate. The On Click event procedure for the button could look like this:

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
    strFile = strPath & "Trinity Solutions.accdb"
    ' Open the other database
    Set dbs = DBEngine.OpenDatabase(strFile)
    ' *** Change the name of the field that you want to add a value to ***
    strSQL = "INSERT INTO tblAdministrativeInfo (FieldName) VALUES ('TurnOffPrintPopup')"
    ' Execute the append query
    dbs.Execute strSQL, dbFailOnError
    ' Close the other database
    dbs.Close
End Sub
Don't forget to change FieldName to the name of the field in tblAdministrativeInfo in which you want to add a value.
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 »

I have a few questions.
1. The path of the database backend with the tables is C:\Trinity Solutions\TrinitySolutionsDatabaseTables. How does the code know that? I wasn't sure if I am supposed to enter that somewhere.
2. When you say "Change FieldName" to the name of the field that I want to add a value" - this is adding a whole new row to the table correct? Not just data to an exisiting field?
3. Is there a way to set the format of the new field?
Thanks!

User avatar
HansV
Administrator
Posts: 78241
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 »

Do you want to add a new field? I thought you wanted to add a value to an existing field. :stupidme:
What kind of field should it be? A Yes/No field?
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 »

I'm so sorry Hans. I guess I wasn't clear. It would be a yes/no field.

User avatar
HansV
Administrator
Posts: 78241
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 »

Try this:

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
    strFile = strPath & "Trinity Solutions.accdb"
    ' Open the other database
    Set dbs = DBEngine.OpenDatabase(strFile)
    ' SQL to add field
    strSQL = "ALTER TABLE tblAdministrativeInfo ADD COLUMN TurnOffPrintPopup YESNO"
    ' Execute the SQL
    dbs.Execute strSQL, dbFailOnError
    ' Close the other database
    dbs.Close
End Sub
The code assumes that the user will place the database with the code in the same folder as the database to be updated. If that is not possible, change the line

Code: Select all

    strPath = CurrentProject.Path
to

Code: Select all

    strPath = "C:\Trinity Solutions\TrinitySolutionsDatabaseTables"
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 »

That is absolutely amazing!! Doing the Snoopy Dance of Joy around my living room. My son thinks I'm nuts!

Don't shoot me................thinking in advance for down the road.....................Is it possible create do this with a table. Say for example lets say I need to add a totally new table to these same 30 databases and I create it in the mini database. What would the code look like to deploy it to their backend?

Thanks so much,
Leesha

User avatar
HansV
Administrator
Posts: 78241
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 »

Here is an example that creates a table tblTest with three fields:

ID (Number, Long Integer)
LastName (Text, max 30 characters)
DateOfBirth (Date)

It also sets ID to be the primary key of the table.

Code: Select all

Private Sub cmdCreate_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
    strFile = strPath & "Trinity Solutions.accdb"
    ' Open the other database
    Set dbs = DBEngine.OpenDatabase(strFile)
    ' SQL to create table
    strSQL = "CREATE TABLE tblTest (ID INTEGER, LastName TEXT(30), DateOfBirth DATE)"
    ' Execute the SQL
    dbs.Execute strSQL, dbFailOnError
    ' SQL to create primary index
    strSQL = "CREATE INDEX PrimaryKey ON tblTest (ID) WITH PRIMARY"
    ' Execute the SQL
    dbs.Execute strSQL, dbFailOnError
    ' Close the other database
    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 »

I am not sleep tonight!!! Thanks ever so much.

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

Re: Add a row to a table from code

Post by Leesha »

I ran the code, didn't get any errors but it didn't create the table? I made sure that I had change this strPath = "C:\Trinity Solutions\TrinitySolutionsDatabaseTables"

User avatar
HansV
Administrator
Posts: 78241
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 »

Click in the navigation pane and press F5 to refresh it. Does the new table appear?
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 »

No that didn't work either. I opened and closed the db with the tables as well and did a search for it but it doesn't show.

User avatar
HansV
Administrator
Posts: 78241
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's strange - when I tested the code, the table didn't automatically appear in the navigation pane, but it did after a refresh.
If the code failed you should have seen an error message.
It's past 2 AM here, I'm going to bed now.
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 »

Well, I went back and relooked at the code and it ran fine. The issue was the name of the click cmdbutton it was on. I'm going to be too. I swear I can't see straight! It is awesome code.

I'm sure you've already guessed my next question. How do I create a link to the table or is that even possible? The code on the small Access database is creating a new table in the backend database but the link needs to be in the frontend database. My head is spinning just thinking about it.
Thanks!
Leesha