Add a row to a table from code
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Add a row to a table from code
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
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
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
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 :-)
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row to a table from code
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?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
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?
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row to a table from code
Will the database that you want to update always have the same name?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
Yes. The database will always be called TrinitySolutions. The backend tables are called TrinitySolutions_BE
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row to a table from code
Let's say you name the command button cmdUpdate. The On Click event procedure for the button could look like this:
Don't forget to change FieldName to the name of the field in tblAdministrativeInfo in which you want to add a value.
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
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!
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!
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row to a table from code
Do you want to add a new field? I thought you wanted to add a value to an existing field.
What kind of field should it be? A Yes/No field?
What kind of field should it be? A Yes/No field?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
I'm so sorry Hans. I guess I wasn't clear. It would be a yes/no field.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row to a table from code
Try this:
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
to
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
Code: Select all
strPath = CurrentProject.Path
Code: Select all
strPath = "C:\Trinity Solutions\TrinitySolutionsDatabaseTables"
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
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
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
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row to a table from code
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.
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
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
I am not sleep tonight!!! Thanks ever so much.
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
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"
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row to a table from code
Click in the navigation pane and press F5 to refresh it. Does the new table appear?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
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.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Add a row to a table from code
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.
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
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Add a row to a table from code
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
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