Using Variables as Table Names

[MMcCarthy]

Try this:

 Function Correspondence_list() 
Dim qdf As QueryDef
Dim strSQL As String
Dim DB As Object
Set DB = CurrentDb
Set qdf = DB.QueryDefs("Doc_Corres_Query")
 
strSQL = "SELECT EE_Ref_no AS [EE Ref#], Trim_Ref_no AS [Trim Ref#], Subject, Correspondence_from AS Correspondence," & _
"FROM " & [contract_no] & ";"
qdf.Execute strSQL
qdf.Close
End Function

BTW, a data type converstion error is where you are trying to pass something that doesn't have the same value type as the object you are trying to pass it into.

What is the value in combobox [contact_no]. I assumed from your previous posts it was the table name. If not, what is it?

Mary

[Killer42]

..."data type conversion error"

Are you sure that's how the .Execute method is used? My guess (I'm no expert in this area, though) is that .Execute doesn't want a string. More likely you need to change the .SQL property, then use .Execute method to run it.

Don't take this as Gospel, though. I'm much more comfortable in VB than Access.

[Killer42]

...What is the value in combobox [contact_no]. I assumed from your previous posts it was the table name. If not, what is it?

I don't suppose the combobox could be tripping you up by having a different default property to what you expect? (Just asking, I have no idea.)

[MMcCarthy]

Are you sure that's how the .Execute method is used? My guess (I'm no expert in this area, though) is that .Execute doesn't want a string. More likely you need to change the .SQL property, then use .Execute method to run it.

Don't take this as Gospel, though. I'm much more comfortable in VB than Access.

I think you're right. It's been a while since I've used this. Damn, going on that memory again.

Try replacing:

qdf.Execute strSQL

with

qdf.SQL = strSQL

If that doesn't work let me know, I'll look it up.

Mary

[MMcCarthy]

What's that? My ears are tingling...

No, it's definitely not higher in Access 2003. I wish... :(

But yes, it depends entirely on what's in the data, and the indexes. I have databases with over 15 million records. On the other hand, these are not very large records.

Hang on, let's do a quick calculation. If we assume 500,000 records then they could average no larger than around 4,294 bytes each - and that includes indexes, wasted space that hasn't been compacted yet, and so on. Judging by the term "correspondence " I'd say it's probably much safer to assume it will grow too large for a single table.

One table per contract does seem excessive, though. You might want to check the online help (or consult mmccarthy) for the maximum number of tables allowed.

Hey, that's what I said! :)

I suspect that what will be required is a normalisation of the correspondence table. Probably broken out over different types of correspondence, each tied to the contracts table by contract number. Without seeing the full structure of the correspondence table I can't make any further guesses.

However, if Number5 would like some assistance on this I would be glad to provide it. Just send me a list of the fields and their datatypes of all fields in the contracts and correspondence tables. Along with an explanation of what the field refers to when its not obvious.

Mary

[Killer42]

I suspect that what will be required is a normalisation of the correspondence table. Probably broken out over different types of correspondence, each tied to the contracts table by contract number. Without seeing the full structure of the correspondence table I can't make any further guesses.

Sounds about right, though database design was never my strong-point.

[NeoPa]

qdf.Execute strSQL

You cant specify a SQL string with this format.
You need the Connection or Database (.Execute) method to pass a SQL string.

Execute Method


Runs an action query or executes an SQL statement on a specified Connection or Database object.

Syntax

object.Execute source, options

querydef.Execut e options

The Execute method syntax has these parts.

Part Description
object A Connection or Database object variable on which the query will run.
querydef An object variable that represents the QueryDef object whose SQL property setting specifies the SQL statement to execute.
source A String that is an SQL statement or the Name property value of a QueryDef object.
options Optional. A constant or combination of constants that determines the data integrity characteristics of the query, as specified in Settings.

[Killer42]

querydef.Execut e options

Hey, what do you know? I was right! :)

[MMcCarthy]

Hey, what do you know? I was right! :)

Had to happen sometime ;)