Update Query from Excel

[wtrimble]

In my database I need to export a query to excel so my coworker can update it. Now I need to create a button that will create an Update Query to update any changed information back into the Access query. How can I go about doing this. I have the following from other sources but somethings not right. Am I close to what I need or am I way off?

Any help will be greatly appreciated!

Dim cn As Object, rs As Object, Status As Range
Dim MySql As String, dbfullname As String, myCnt As Long
dbfullname = "P:\Databases\Access Database Development\6.TestDatabases\testtable.mdb"
Set Status = Sheets("testquery").Range("A2") 'SQL Variable
MySql = "SELECT ProjectID,ProjectInfo.OppId,Po_Value,ProjectInfo.Submitter,Comments, " _
& "OppTable.OppId,Customer,Plant,OppTable_Submitter " _
    & "FROM testquery WHERE " & _
    "projectid ='" & Status & "';" 'Stack your SQL string
Status = Empty 'Clear SQL variable string
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " _
       & dbfullname & ";"  'Create DB connection
 
Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = MySql 'Pass your SQL
   .Open , , 3, 3
 
    myCnt = .RecordCount
 
    .Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub

 

[wtrimble]

Anyone have an idea on how to go about doing it??

 

[wtrimble]

How can I connect to Access from Excel. If I know that I think I write code to go line for line in excel and update my DB that way.

 

[Trevor G]

In Excel you can use the Data Menu if using Office 2003 and you can find create a query which will allow you to connect to an Access Database there is a wizard so it is fairly straight forward.

If using Office 2007 then you can use the Data Tab and on the left there is an option to get from Access.