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
>