Dynamically build an Access query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

In VBA, my code is importing from an XLS file, the format of which I don't
always know, as the columns which create the XLS are user definable.

What I am doing is importing that XLS into a temp table, which is created
during that import.

Here is the question: I then want to write a query which takes "certain"
fields I know are of interest, and use them in an append query. Of the 20
fields I need, the imported file may have more, but that's OK, as those
fields are not in the Append Query. The problem I have is this: Of the 20
fields I need, the use may not always included those in the XLS. Access asks
me for the value to use when it tries to append a field that does not exist.
So, I am looking for a good way to dynamically create the SQL/Query to be
based on those fields of interest that are actually in the imported table.

Thanks

-David
 
This will take a little doing, but it can be done. I will assume the 20
fields you are talking about are all the fields in the table you want to
append to. To do this, you will need to define TableDef objects for both of
the tables, the append to table and the temp table from XL.
The technique is to loop through the tables in the XL temp table and look at
each field name and see if it exists in the Production table. If it does,
then add it to your SQL string. Once the SQL string is completed, you can
save it useing the CreateQueryDef method.

Dim qdfs As QueryDefs
Dim qdf As QueryDef
Dim tdfs As TableDefs
Dim tdfXL As TableDef
Dim tdfProd As TableDef
Dim dbf As Database
Dim fld As Field
Dim strFldName as String
Dim strSQL as String
Dim strInsert as String
Dim strSelect as String

Set dbf = CurrentDb
Set qdfs = CurrentDb.QueryDefs

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_TempXL" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

Set tdfs = dbf.TableDefs
Set tdfXL = tdfs("XLTableName")
Set tdfProd = tdfs("ProductionTableName")
For Each fld In tdfXL.Fields
strFldName = fld.Name
For Each fld in tdfProd.Fields
If fld.Name = strFldName Then
strInsert = strInsert & strFldName & ","
strSelect = strSelect & strFldName & ","
Exit For
End If
Next fld
Next fld

strInsert = Left(strInsert, Len(strInsert) -1)
strSelect = Left(strSelect, Len(strSelect) -1)
strSQL = "INSERT INTO tblProduction (" & strInsert & _
"SELECT " & strSelect & " FROM tblXL;"

Set qdf = dbf.CreateQueryDef("_TempXL", strSQL)

Set dbf = Nothing
Set tdfs = Nothing
Set tdfXL = Nothing
Set tdfProd = Nothing
Set qdfs = Nothing
Set qdf = Nothing

Note, the above is untested air code and make take some tweeking to work
correctly, but it is the main concept of how to do this.
 
That did the trick.

Thanks so much.
--
David


Klatuu said:
This will take a little doing, but it can be done. I will assume the 20
fields you are talking about are all the fields in the table you want to
append to. To do this, you will need to define TableDef objects for both of
the tables, the append to table and the temp table from XL.
The technique is to loop through the tables in the XL temp table and look at
each field name and see if it exists in the Production table. If it does,
then add it to your SQL string. Once the SQL string is completed, you can
save it useing the CreateQueryDef method.

Dim qdfs As QueryDefs
Dim qdf As QueryDef
Dim tdfs As TableDefs
Dim tdfXL As TableDef
Dim tdfProd As TableDef
Dim dbf As Database
Dim fld As Field
Dim strFldName as String
Dim strSQL as String
Dim strInsert as String
Dim strSelect as String

Set dbf = CurrentDb
Set qdfs = CurrentDb.QueryDefs

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_TempXL" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

Set tdfs = dbf.TableDefs
Set tdfXL = tdfs("XLTableName")
Set tdfProd = tdfs("ProductionTableName")
For Each fld In tdfXL.Fields
strFldName = fld.Name
For Each fld in tdfProd.Fields
If fld.Name = strFldName Then
strInsert = strInsert & strFldName & ","
strSelect = strSelect & strFldName & ","
Exit For
End If
Next fld
Next fld

strInsert = Left(strInsert, Len(strInsert) -1)
strSelect = Left(strSelect, Len(strSelect) -1)
strSQL = "INSERT INTO tblProduction (" & strInsert & _
"SELECT " & strSelect & " FROM tblXL;"

Set qdf = dbf.CreateQueryDef("_TempXL", strSQL)

Set dbf = Nothing
Set tdfs = Nothing
Set tdfXL = Nothing
Set tdfProd = Nothing
Set qdfs = Nothing
Set qdf = Nothing

Note, the above is untested air code and make take some tweeking to work
correctly, but it is the main concept of how to do this.
Click to expand...
 
Back
Top