vba - Create Microsoft Access SQL queries on the fly -

i need link circa 270 (amount can change) .txt files , create select query on of them. can import them @ once no problem, access can handle 32 tables per select query need way create variable (on fly?) hold sql string used parameter in create query def function. have ideas. i'm thinking need use class, there easier way?

my approach in similar situation put of input files in common folder, nothing else in folder. use following vba script process files. each file, add file name directory table (".import files") , data second table (".import data"). "source" field in data link "index" field of files list know data came each source file. doesn't matter how many files in folder. (if prefer, can change 1 of 2 tables these 2 fields have same name if had been thinking ahead more, have done so.)

my input files aren't particularly formatted (and cannot control data source), @ least in same poor format. specifically, there not field names in first row of data. that's why import them temporary file, , in "insert into" command has fields in source table f1, ... f9. you'll need change lines build sql "insert into" statement match file formats.

you need create directory , data files before run code - can create data file importing 1 of source files template, editing add "source" field. 3 fields in ". import files" table index (which auto-number field), file name , update date.

when done this, you'll have 2 tables can join on "source" (from .import data) , "index" (from .import files). can continue whatever processing require. can select on "source" @ specific input file, or group "source" summarize data input, or ignore "source" summary of of data.

    function import_btt()      'on error resume next     docmd.setwarnings false      'get folder & file list     source_folder = get_folder()     set fso = createobject("scripting.filesystemobject")     set flist = fso.getfolder(source_folder).files      'delete prior data         set db = currentdb()         db.execute ("delete * [book tax import files];")         db.execute ("delete * [book tax import data];")      'process each file     each file in flist         if (len(dir(source_folder & "\" & file.name)) = 0) goto nextfile          'add new data         db.execute "insert [book tax import files] ([file name],[update date]) values (" & "'" & file.name & "',#" & file.datelastmodified & "#);"         source = db.openrecordset("select @@identity")(0)         docmd.transfertext , , "book tax import temp", source_folder & "\" & file.name          sql = "insert [book tax import data] (source, account, [book balance], [book adjustments], [adjusted book balance], [tax reclass], [balance after tax reclass], [tax adjustments], [historical tax adjustments], [tax balance] )"         sql = sql & "select '" & source & "',mid(f1,instr(f1,'(')+1,10),f2,f3,f4,f5,f6,f7,f8,f9 [book tax import temp] ((trim(f1) 'bk (*') or (trim(f1) 'tax (*'));"          docmd.runsql sql         docmd.deleteobject actable, "book tax import temp"      nextfile: next file      msgbox ("data import completed")     docmd.setwarnings true      end function  public function get_folder()  'create filedialog object folder picker dialog box. const msofiledialogfolderpicker = 4 const msofiledialogfilepicker = 3 const msofiledialogviewdetails = 2  set fd = application.filedialog(msofiledialogfolderpicker) fd.allowmultiselect = false fd.buttonname = "select" fd.initialview = msofiledialogviewdetails fd.title = "select folder" fd.initialfilename = "mydocuments\" fd.filters.clear  'show dialog box , file name if fd.show = -1     get_folder = fd.selecteditems(1)     else     get_folder = "mydocuments\"     end if  set fd = nothing end function