Link to home
Create AccountLog in
Avatar of Futureman9
Futureman9

asked on

Why does SQL Append only work at times

Help Experts:

I run the sub to open another access database.

I want to change a field on the outside database table.

I copy my blank new table to the outside database.

Try to run sql to append records from old table to new blank table.

I delete the old table

Try to rename the new table.

When I step through the code in debug mode it works perfectly.

When I run it at full speed it sometimes fails to append any records to the new blank table.

Does the sql need to pause????
Is something running too fast???
Sub Update_Programs() 'update programs
 
Dim qdf As Object, newDBPath, j As Object, frm As Object, rpt, mdl, dbs As Object
Dim db As DAO.Database
Dim dbslinkfrom As Object
Dim dbslinkto As Object
Dim mysql As String
Dim tablfromupdate As String
Dim tablintarget As String
 
 
On Error Resume Next
 
dbtarget = "c:\access\xxx.mdb"
dbtargetbe = "c:\access\xxx_be.mdb"
bePath = "C:\access\xxx_be.mdb"
Set dbs = OpenDatabase(bePath)
 
 
tablfromupdate = "copy of customer"
tablintarget = "customer"
 
dbs.TableDefs.Delete (tablfromupdate)
 
DoCmd.TransferDatabase acExport, "Microsoft Access", dbtargetbe, acTable, tablfromupdate, tablfromupdate
'dbs.TableDefs.Refresh
 
MsgBox "Count to 3 then click the cancel button"
 
mysql = "INSERT INTO [Copy Of Customer] ( ID, [Last], [First], Created, Sitename, Hotcomname, Sitepass, Billaddress, Signdate, City, State, Country, Zip, Billstatus, Lastbillamt, Lastbilldate, Nexactdate, PWCancelDate, Stdrate, Discrate, Discend, Email, Phone, [CC#], Exp, [CVV#], Field25, Field26, [How Did User Hear About Us], Welcome, [Gift Status], Notes )"
mysql = mysql + " SELECT Customer.ID, Customer.Last, Customer.First, Customer.Created, Customer.Sitename, Customer.Hotcomname, Customer.Sitepass, Customer.Billaddress, Customer.Signdate, Customer.City, Customer.State, Customer.Country, Customer.Zip, Customer.Billstatus, Customer.Lastbillamt, Customer.Lastbilldate, Customer.Nexactdate, Customer.PWCancelDate, Customer.Stdrate, Customer.Discrate, Customer.Discend, Customer.Email, Customer.Phone, Customer.[CC#], Customer.Exp, Customer.[CVV#], Customer.Field25, Customer.Field26, Customer.[How Did User Hear About Us], Customer.Welcome, Customer.[Gift Status], Customer.Notes"
mysql = mysql + " FROM Customer;"
 
dbs.Execute mysql
 
dbs.TableDefs.Delete (tablintarget)
 
dbs.Close
Set dbs = Nothing
 
Set dbslinkfrom = CreateObject("access.application")
dbslinkfrom.OpenCurrentDatabase dbtargetbe
' dbslinkfrom.Visible = True
dbslinkfrom.DoCmd.Rename tablintarget, acTable, tablfromupdate
dbslinkfrom.CloseCurrentDatabase
dbslinkfrom.Quit
 
End Sub

Open in new window

Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

What exactly are you trying to do with this code? Are you trying to update a local table with data from a remote table?
Avatar of Futureman9
Futureman9

ASKER

We have a billing and updates customer information. I use a front end and back end database.

From the help here I figured out how to send report and form updates and changes. I use a very small database called xxxchange that runs sub() to delete and replace forms etc.

Now we want to change a field on the customer table. The field is calle [how did you hear about us]. We run a daytrading service and want to know where best to promote it.

Right now 10 different customers can say the same thing yet express it differently. They could say "searched for you on google" or "goog" etc

We want to change the free form text file to link to a table that allows the combo box to limit to list. I first need to copy all cust records, then delete old table and rename new.
Modify this line (25 in your code s nippet):
DoCmd.TransferDatabase acExport, "Microsoft Access", dbtargetbe, acTable, tablfromupdate, tablfromupdate

To:
DoCmd.TransferDatabase acExport, "Microsoft Access", dbtargetbe, acTable, tablintarget , tablfromupdate
Sorry hnasr.
Tried it. It copied the blank [copy of customer] table to the target database and overwrote the [customer] table. So the target customer table is blank, but the new field is there.

Why I kept the same name in the DoCmd.Transfer was to have 2 tables on the target database. One called Copy of Customer and the original Customer. The Copy of customer has the proper field but no records. Then I try to SQL append all the customer records from the customer to the copy of customer table.
Then I delete the Customer table.
Then I rename the Copy of Customer table to Customer and all is well.
The strange thing is when I step through the whole Sub() procedure in debug mode it always does everything correctly.
It is when I call the program by means of the Switchboard, it runs through and only works about 50% of the time. There are no error messages. But sometimes I just have a blank customer table.
Half solved the problem but I don't think I will win programmer of the year.

1) Eliminated an on error resume next.
2) Had program check for existing [copy of customer table] before automatically trying to delete it.

3) This is the weird one.  Right after copying the blank table to the target database I inserted :
MsgBox "Count to 3 then click the cancel button"

It seems that the following SQL append statement could not quickly find the [copy of customer] table, until I gave it time to do so!  Is this normal??

I don't think MS Flight Simulator asks you to go have a drink before switching scenery.

Is there a more elegant way to have the software timeout without the user knowing about it? or is my fix not really a fix?
Sub Update_Programs() 'update programs
 
Dim qdf As Object, newDBPath, j As Object, frm As Object, rpt, mdl, dbs As Object
Dim db As DAO.Database
Dim dbslinkfrom As Object
Dim dbslinkto As Object
Dim mysql As String
Dim tablfromupdate As String
Dim tablintarget As String
 
dbtarget = "c:\access\xxx.mdb"
dbtargetbe = "c:\access\xxx_be.mdb"
bePath = "C:\access\xxx_be.mdb"
Set dbs = OpenDatabase(bePath)
 
tablfromupdate = "copy of customer"
tablintarget = "customer"
 
For x = 0 To dbs.TableDefs.Count - 1
If dbs.TableDefs(0).Name = tablfromupdate Then
dbs.TableDefs.Delete (tablfromupdate)
End If
Next x
 
DoCmd.TransferDatabase acExport, "Microsoft Access", dbtargetbe, acTable, tablfromupdate, tablfromupdate
 
dbs.TableDefs.Refresh
 
MsgBox "Count to 3 then click the cancel button"
 
mysql = "INSERT INTO [Copy Of Customer] ( ID, [Last], [First], Created, Sitename, Hotcomname, Sitepass, Billaddress, Signdate, City, State, Country, Zip, Billstatus, Lastbillamt, Lastbilldate, Nexactdate, PWCancelDate, Stdrate, Discrate, Discend, Email, Phone, [CC#], Exp, [CVV#], Field25, Field26, [How Did User Hear About Us], Welcome, [Gift Status], Notes )"
mysql = mysql + " SELECT Customer.ID, Customer.Last, Customer.First, Customer.Created, Customer.Sitename, Customer.Hotcomname, Customer.Sitepass, Customer.Billaddress, Customer.Signdate, Customer.City, Customer.State, Customer.Country, Customer.Zip, Customer.Billstatus, Customer.Lastbillamt, Customer.Lastbilldate, Customer.Nexactdate, Customer.PWCancelDate, Customer.Stdrate, Customer.Discrate, Customer.Discend, Customer.Email, Customer.Phone, Customer.[CC#], Customer.Exp, Customer.[CVV#], Customer.Field25, Customer.Field26, Customer.[How Did User Hear About Us], Customer.Welcome, Customer.[Gift Status], Customer.Notes"
mysql = mysql + " FROM Customer;"
 
dbs.Execute mysql
 
dbs.TableDefs.Delete (tablintarget)
 
dbs.Close
Set dbs = Nothing
 
Set dbslinkfrom = CreateObject("access.application")
dbslinkfrom.OpenCurrentDatabase dbtargetbe
dbslinkfrom.Visible = True
dbslinkfrom.DoCmd.Rename tablintarget, acTable, tablfromupdate
dbslinkfrom.CloseCurrentDatabase
dbslinkfrom.Quit
 
 End Sub

Open in new window

It would seem all you need is to just add a Column to a table ... You can add a column to a table using this type of Syntax:

ALTER TABLE table_name
ADD column_name datatype

From here, your FE should handle the rest, unless you need to move existing records for that free-form text to another table, or somehow transpose those records ... if you need to remove that column, you can do this:

ALTER TABLE table_name
DROP COLUMN column_name

So all you'd need would be a connection to your database:

Dim dbs As DAO.Database
Set dbs = OpenCurrentDatabase "path to your backend"
dbs.Execute "ALTER TABLE ADD COLUMN blah blah"

And am I correct in hearing that you are migrating form/report changes in this manner? And you mention in your post that you have a split database? If so, why? Just copy the new FE over the old and be done with it ...


Hi LSM,
I am ready to update the Front end the way you suggest. In the past I guess was just afraid that I would overwrite something needed by the user in North Carolina.

I started up reading the ALTER TABLE table_name
ADD column_name datatype  method

the info I have been able to find on this site and in books did not seem to be able to teach me enough about the various datatypes or properties I would have to change on one particular field.
Like the text field now that becomes a combo box with a source property etc. I do not know the syntax except for the simple ADD Column text.  It is the blah blah part that I need to learn. LOL

I admit it would be easier because then all I would have to do is copy the stuff in the old field to the new one then delete the column.
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Giving this a shot now Hnasr:
Thanks Hnasr:

The new SQL worked. Because of my inexperience it took me a while to figure out why I was getting a parameter error. I had to change the customer.t to customer.ID

I will not be able to get to this for another 2 days to add the other required fields to the SQL. I am sure it will work. I was not aware a Do.cmd could manipulate an sql within another database.

Is it possible to use a Do.Cmd to delete a table and rename a table in a second database? I could eliminate the dbs.tabledefs.delete command from my code.

Thank you. I am awarding the credit now. If a second problem arises I will post another question.