What methods are you using? YOu can read data from an Access Database
using q query, but you can't write the data back using a Query. I would
go to the Access VBA help and look up ADO method. YO ucan Use the Access
VBA language in Excel if you declare two references in the VBA menu

Tools - REference

Add the following by cliking the check box in the refernce menu and
then pressing OK

1) Microsoft Access 11.0 object library (or latest version on you PC)
2) Microsoft ActiveX daa objects 2.8 library (or latest version on you
PC)


here is some sample code

Sub MoveData()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set sourcesht = ThisWorkbook.Sheets("Sheet1")
Folder = "c:\Temp\"
DestFile = Folder & "Activity overview1.xls"
'excel worksheet must have dollar sign at end of name
DestShtName = "Sheet1" & "$"

With sourcesht
Person = .Range("A1")
EstWorkLoad = .Range("C4")
RealWorkLoad = .Range("C5")
WeekNum = .Range("F2")
End With

'open a connection, doesn't open the file
Set cn = New ADODB.Connection

With cn

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DestFile & ";" & _
"Mode=Share Deny None;" & _
"Extended Properties=""Excel 8.0;HDR=No;ReadOnly=False;"""

Open (ConnectStr)
End With

'open the recordset
Set rs = New ADODB.Recordset
With rs

MySQL = "SELECT * FROM [" & DestShtName & "] "

Open Source:=MySQL, _
ActiveConnection:=cn

If .EOF < True Then

RowCount = 1
Do While Not .EOF And RowCount < 14

MoveNext
RowCount = RowCount + 1
Loop

If .EOF Then
MsgBox ("Not Enough Rows - Exit macro")
End If

setLoad = ""

WorkWeekCol = 0
WorkWeek = 22
For Each Fld In rs.Fields
If Fld.Value = WorkWeek Then
'rows and columns are backwards from excel
WorkWeekCol = Range(Fld.Name).Row
Exit For
End If
Next Fld
End If

If WorkWeekCol = 0 Then
MsgBox ("Did not find WorkWeek : " & WorkWeek & ". Exiting
Macro")
Exit Sub
End If

Close

Person = "Joel"

MySQL = "SELECT *" & vbCrLf & _
"FROM [" & DestShtName & "] " & vbCrLf & _
"Where [" & DestShtName & ".F1]='" & Person & "'"

Open Source:=MySQL, _
ActiveConnection:=cn, _
LockType:=adLockOptimistic, _
CursorType:=adCmdTable

If .EOF = True Then
MsgBox ("count not find : " & Person & " Exit Macro")
Exit Sub
Else

EstWorkLoad = 123
RealWorkLoad = 456
'field start at zero, subtract one from index
Fields(WorkWeekCol - 1).Value = EstWorkLoad
Fields(WorkWeekCol).Value = RealWorkLoad
Update
End If


End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165269

Microsoft Office Help