Mike,

There are no comma's in the SQL statment as the field names are all
constants that are defined else where, and they are defined with the coma and
a space in them. The last constant used goes thru a function called
TRIM_FIELD which will change the string length by -2 to take off the comma
and the space.

I did it this way as the field names are used by approx 20 different
extract's and if the names change it is alot easier to change it once, then
have to do it lot's of times.

--
Cheers...


"Mike" wrote:

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...