Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Recorded SQL Query to "Insert, Delete, Update"
I have an sample Access Database. It has 1 table w/ 4 Fields. I have
recorded the following code using Excel's Macro Recorder & MS Query: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=C:\Temp.mdb;DefaultDir=C:\Temp;Driver={M icrosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBuffe" _ ), Array( _ "rSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransa ctions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _ )), Destination:=Range("A1")) .Sql = Array( _ "SELECT Data.EEID, Data.FirstName, Data.LastName, Data.HireDate" & Chr(13) & "" & Chr(10) & "FROM `C:\Temp`.Data Data" _ ) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With End Sub I looked into MS Query - it does not appear to allow insert, delete, update, etc. functionality, So I don't know how to modify the above code (or record a macro) to "Insert", "Update" or "Delete" Records rather than "Select" Records. I did read your posts code located at www.bygsoftware.com & that code does work if I add the DAO 3.6 Object Library Reference. Since I am not a programmer, I was hoping not to have to maintain various types of code. Question: Can the above code even be modified to do what I am asking? Thank you for your assistance. -- Thx MSweetG222 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Recorded SQL Query to "Insert, Delete, Update"
Afraid it is not so easy. MSQuery, and thus the macro you recorded, is
designed to return a set of results to your spreadsheet (as a QueryTable in code, which is what you are working with); a querytable is not designed for queries that modify the data in the database. For that you will need the more sophisticated and flexible tools of ADO (and I recommend ADO - ActiveX Data Objects, over DAO - Data Access Objects. DAO is being phased out as ADO takes its place). With your statement "I am not a programmer, I was hoping not to have to maintain various types of code" I am not sure how much you want to get into this - you can accomplish what you want to do without too much actual programming but there are some conceptual things you need to learn to be able to make the leap. If you are interested in at least seeing what is involved, check this link: http://msdn.microsoft.com/library/de...tedwithADO.asp -- - K Dales "MSweetG222" wrote: I have an sample Access Database. It has 1 table w/ 4 Fields. I have recorded the following code using Excel's Macro Recorder & MS Query: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=C:\Temp.mdb;DefaultDir=C:\Temp;Driver={M icrosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBuffe" _ ), Array( _ "rSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransa ctions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _ )), Destination:=Range("A1")) .Sql = Array( _ "SELECT Data.EEID, Data.FirstName, Data.LastName, Data.HireDate" & Chr(13) & "" & Chr(10) & "FROM `C:\Temp`.Data Data" _ ) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With End Sub I looked into MS Query - it does not appear to allow insert, delete, update, etc. functionality, So I don't know how to modify the above code (or record a macro) to "Insert", "Update" or "Delete" Records rather than "Select" Records. I did read your posts code located at www.bygsoftware.com & that code does work if I add the DAO 3.6 Object Library Reference. Since I am not a programmer, I was hoping not to have to maintain various types of code. Question: Can the above code even be modified to do what I am asking? Thank you for your assistance. -- Thx MSweetG222 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Recorded SQL Query to "Insert, Delete, Update"
K Dales - thank you for your response. I will check out the link.
-- Thx MSweetG222 "K Dales" wrote: Afraid it is not so easy. MSQuery, and thus the macro you recorded, is designed to return a set of results to your spreadsheet (as a QueryTable in code, which is what you are working with); a querytable is not designed for queries that modify the data in the database. For that you will need the more sophisticated and flexible tools of ADO (and I recommend ADO - ActiveX Data Objects, over DAO - Data Access Objects. DAO is being phased out as ADO takes its place). With your statement "I am not a programmer, I was hoping not to have to maintain various types of code" I am not sure how much you want to get into this - you can accomplish what you want to do without too much actual programming but there are some conceptual things you need to learn to be able to make the leap. If you are interested in at least seeing what is involved, check this link: http://msdn.microsoft.com/library/de...tedwithADO.asp -- - K Dales "MSweetG222" wrote: I have an sample Access Database. It has 1 table w/ 4 Fields. I have recorded the following code using Excel's Macro Recorder & MS Query: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=C:\Temp.mdb;DefaultDir=C:\Temp;Driver={M icrosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBuffe" _ ), Array( _ "rSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransa ctions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _ )), Destination:=Range("A1")) .Sql = Array( _ "SELECT Data.EEID, Data.FirstName, Data.LastName, Data.HireDate" & Chr(13) & "" & Chr(10) & "FROM `C:\Temp`.Data Data" _ ) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With End Sub I looked into MS Query - it does not appear to allow insert, delete, update, etc. functionality, So I don't know how to modify the above code (or record a macro) to "Insert", "Update" or "Delete" Records rather than "Select" Records. I did read your posts code located at www.bygsoftware.com & that code does work if I add the DAO 3.6 Object Library Reference. Since I am not a programmer, I was hoping not to have to maintain various types of code. Question: Can the above code even be modified to do what I am asking? Thank you for your assistance. -- Thx MSweetG222 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Disabling "Entire &row/&column" in Delete/Insert Cell | Excel Programming | |||
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming | |||
SQL "INSERT INTO" Does not Modify Definition of Target Range | Excel Programming |