Modify Recorded SQL Query to "Insert, Delete, Update"

[MSweetG222]

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(Array( _
"ODBC;DBQ=C:\Temp.mdb;DefaultDir=C:\Temp;Driver={Microsoft Access
Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBuffe" _
), Array( _

"rSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=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 re: 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

[K Dales]

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(Array( _
> "ODBC;DBQ=C:\Temp.mdb;DefaultDir=C:\Temp;Driver={Microsoft Access
> Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBuffe" _
> ), Array( _
>
> "rSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=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 re: 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
>

[MSweetG222]

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(Array( _
> > "ODBC;DBQ=C:\Temp.mdb;DefaultDir=C:\Temp;Driver={Microsoft Access
> > Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBuffe" _
> > ), Array( _
> >
> > "rSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=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 re: 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
> >