dynamic column names

[johnalexander]

Hello,
I am linking to tab delimted text files, which are not always consistent (i.e.-sometimes columns will be missing altogether upon refresh, causing problems with the linked data). I think a work around would be to set up the link with more fields than I will ever need and do NOT include the first row as column names. Then, I will need to somehow bring the first row as the column names in a second step, but I do not know how to do this.

To re-state, I DO know how to make the first row the column names in the wizard, but this won't work for me because if the linked table is refreshed, and a column is completely omitted, then the data will not be lined up properly with the proper column names.

I am sure there must be a way to move the first row into the column names in a second step.

Please help...

 

[johnalexander]

I may have found a solution by using the "send keys" component of Macro. This seems to work perdfect all the way up to the end, where doesn't seem to like my final {enter} command (when it sends the final confirmation dialog box-I send an {enter}. Is there some reason why all the other sendkeys work except that one?

Please.

 

[mdmilner]

Yes, this just takes a different approach.
Try importing the file into a temporary file first and then using an Append Query to send the data to the data table.

What you'll need to do is write a VBA routine that iterates through the field names and 'assembles' your Append query.

Public Sub AssembleSQL()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strFld As String
Dim x As Integer

Set dbs = CurrentDB()

strSQL = "SELECT * FROM tblName"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
  Do Until rs.EOF
    For x = 0 to ((.Fields.Count) -1) 
      strFld = strFld & .Fields(x).Name & ","   ' Each Fieldname
    Next x
    strFld = Left(strFld,len(strFld)-1)   ' Removes final comma
    .MoveNext
  Loop
End With

' Assemble Append Query

strSQL = "INSERT INTO tblDest ( " & strFld &  " ) "
strSQL = strSQL & "SELECT " & strFld & " FROM tblImport"
DoCmd.RunSQL strSQL

Set rs = Nothing
Set dbs = Nothing
End Sub

Assuming all of the fieldnames are already in tblDest (or whatever you wish to call it) this should work every time. Something you might wish to consider is this. This might be more appropriate to slide it into two tables. The first would contain a unique key field and all fields that are always present in every record. The second would have a one-to-many relationship with this primary key field and have only two fields. First would be the name of the column and the second the value that belongs there.

You could import it the same way, but the export operation would look a little different. It also requires a slightly more complex approach to get data out of the two tables. Think of it as something to try out after you perfect the above process.

You should also look at the DoCmd.TransferText method to automate the import process within a VBA procedure.

Mike

 

[johnalexander]

Mike,

Thanks. I really appreciate your help, but that looks a little complex for my VBA level and also I'll be doing this for a number of txt files, which will be cumborsome to maintain. I feel really close with the "send keys" Macro action below. I set Warnings to "no" but even with the final "~" (same as {enter}), it still makes me click "OK" in response to the message "finished linking table...". It does link the table when I manually click OK, but I won't be able to make it do 20 or so txt files.

I must be missing something, because my understanding is that these hardcoded keystrokes are supposed to be taken literally.

SendKeys
keystrokes:%fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%kdnrn%nPitch 35P%fy~
Wait: yes