Hello, and thanks in advance. I have a connection to a database, where I add two fields. Once the two fields are added I would like to update them to their correct value.
May I add the SQL statement in my current procedure and add the updates or do I need to create a New Connection to run the update? I have never used the execute command and I am lost on doing so.
'* update records here to their correct value
strSQLMilitary = "UPDATE table1 IN " & strDatabase & " SET table1.Military = True WHERE (((table1.modNumber) In (SELECT MODNumber FROM tblUpdate WHERE Military=True)));"
Also, do I need to reference the columns in the connection using ADOX? I tested my update query on a linked table and it works I need to now convert it to the active conection.
Table1 is the table name
strDatabase is the path and name to the database C:\MyDatabase.mdb
modNumber is the PK
tblUpdate is the table with the correct data in it.
Also, let me mention I do have a proper connection at this point when I attempt to execute the update
cnn.execute (strSQLMilitary)
my code breaks. What am I missing?
'*Purpose: make a connection to our backend database so we can add
' new fields to it. This procedure relys on three functions
' MakeBackup, AddPersonalData and dhFileExists.
'========================================================================
' MakeBackup copies our database to a new folder just in case we
' need to restore it. It calls dhFileExists to see if we already
' have a file in the backup folder by that name and if so prompts
' the user asking if they want to overwrite.
'========================================================================
' AddPersonalData opens up the main database, which holds the
' users personal data and add the required fields into the proper
' table.
'========================================================================
On Error GoTo DefaultHandler
'* make an ADO connection here
Dim cnn As ADODB.Connection
Dim strConnect As String
Dim strDatabase As String
'* work on adding our fields here.
Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cl As ADOX.Column
'Dim pp As Property
Dim strSQLMilitary As String
'* our flag indicating we already have the field added
'* so we want to drop through our code.
Dim blnFound As Boolean
'* our flag indicating if the patch has been previously
'* installed along with the message informing the user of
'* that fact then act upon their response.
Dim strMsg, Style, Title, UserResponse
Dim response As Integer
Dim blnPatched As Boolean
Life's a journey enjoy the ride...
jazzz
May I add the SQL statement in my current procedure and add the updates or do I need to create a New Connection to run the update? I have never used the execute command and I am lost on doing so.
'* update records here to their correct value
strSQLMilitary = "UPDATE table1 IN " & strDatabase & " SET table1.Military = True WHERE (((table1.modNumber) In (SELECT MODNumber FROM tblUpdate WHERE Military=True)));"
Also, do I need to reference the columns in the connection using ADOX? I tested my update query on a linked table and it works I need to now convert it to the active conection.
Table1 is the table name
strDatabase is the path and name to the database C:\MyDatabase.mdb
modNumber is the PK
tblUpdate is the table with the correct data in it.
Also, let me mention I do have a proper connection at this point when I attempt to execute the update
cnn.execute (strSQLMilitary)
my code breaks. What am I missing?
'*Purpose: make a connection to our backend database so we can add
' new fields to it. This procedure relys on three functions
' MakeBackup, AddPersonalData and dhFileExists.
'========================================================================
' MakeBackup copies our database to a new folder just in case we
' need to restore it. It calls dhFileExists to see if we already
' have a file in the backup folder by that name and if so prompts
' the user asking if they want to overwrite.
'========================================================================
' AddPersonalData opens up the main database, which holds the
' users personal data and add the required fields into the proper
' table.
'========================================================================
On Error GoTo DefaultHandler
'* make an ADO connection here
Dim cnn As ADODB.Connection
Dim strConnect As String
Dim strDatabase As String
'* work on adding our fields here.
Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cl As ADOX.Column
'Dim pp As Property
Dim strSQLMilitary As String
'* our flag indicating we already have the field added
'* so we want to drop through our code.
Dim blnFound As Boolean
'* our flag indicating if the patch has been previously
'* installed along with the message informing the user of
'* that fact then act upon their response.
Dim strMsg, Style, Title, UserResponse
Dim response As Integer
Dim blnPatched As Boolean
Life's a journey enjoy the ride...
jazzz