Should there be commas in your sql??
"SELECT " & Change_No & ", " & Category & ", " & Creation_Date & ", " &
Implem_Date & ", " & Status & ", " & Requestor_Name & ", " & Team & ", " &
Short_Desc & ", " & Trim_Field(Service_Variance) & " FROM " & Input_Table & "
WHERE [" & Trim_Field(Change_No) & "] = '" & MyFieldValue1 & "'"

"Deke" wrote:

Hi, Tim...

Here's the code I'm using, the first sub is called from a for loop, going
thru array contains record numbers :-

Public Sub Business_Affected_Change_Data_Extract(Change_Numbe r As String,
Headings_Required As Boolean)
EO_Data
GetDataForBusAffect Change_Number, Sheets(Report_Type & "
Changes").Range("A" & Last_Row), _
Headings_Required
End Sub


Public Sub GetDataForBusAffect(MyFieldValue1 As String, DestSheetRange As
Range, FieldNames As Boolean)

If FieldNames Then
MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
MyConnection = MyConnection & "Data Source=" & Data_Folder &
Input_Database & ";"
End If
Set MyDatabase = CreateObject("adodb.recordset")
MySQL = "SELECT " & Change_No & Category & Creation_Date & Implem_Date &
Status & Requestor_Name & Team & Short_Desc & Trim_Field(Service_Variance) &
" FROM " & Input_Table & " WHERE [" & Trim_Field(Change_No) & "] = '" &
MyFieldValue1 & "'"
On Error GoTo ErrorHandler
MyDatabase.Open MySQL, MyConnection, 0, 1, 1
If Not MyDatabase.EOF Then
If FieldNames Then
For col = 0 To MyDatabase.Fields.Count - 1
DestSheetRange.Offset(0, col).Value =
MyDatabase.Fields(col).Name
Next
DestSheetRange.Offset(1, 0).CopyFromRecordset MyDatabase
Else
DestSheetRange.CopyFromRecordset MyDatabase
End If
Data_Exists = True
Else
If Extraction_Count 1 And Data_Exists = True Then
Data_Exists = True
Else
Data_Exists = False
End If
End If
If Last_Extraction Then
MyDatabase.Close
Set MyDatabase = Nothing
End If
Exit Sub

ErrorHandler:
If Not MyDatabase Is Nothing Then
If MyDatabase.State = adStateOpen Then MyDatabase.Close
End If
Set MyDatabase = Nothing
If Err < 0 Then
MsgBox Err.Source & "--" & Err.Description, , "Error"
End If

As I said it does work, but it is very slow...

--
Cheers...


"Tim Williams" wrote:

Any code you'd like to share ?

Tim

"Deke" wrote in message
...
I hope someone can help this problem is driving me nuts...

I am trying to do multiple read's against an Access database, based on the
contents of an array, I am using the code from Ron de Bruin's website, and
it's working fine. The only problem I have is it is very very very slow
doing the read's.

I think I saw a post on the forum a couple of weeks ago about something to
do with opening and closing connection to the database, but I can't find
it
again.

I hope someone can help, or I think I'm going to go mad.... :(

--
Cheers...