That's not how SQL works nor how tables work. Not to get all Matrixy on you, but there are no columns. You only see columns because that's best way to display data to a human via a computer and how our brain can "understand" the whole system in a simple way. To reference data in SQL you must use field names.
With that said, I am certain you could hack together some VBA to generate a dynamic query that allows you to reference columns by numbers, but ultimately the SQL it produces would use column names.
We have health plan data file that need to be imported in to MS Access table. The column header names keeping changing or not constant/fixed
but the column location/sequence/order is fixed. So, instead of selecting/reading the columns by column names/header names, I would like
to read the columns by column number/column locations and import it into exsiting table or drop existing table & create new table.
We have health plan data file that need to be imported in to MS Access table. The column header names keeping changing or not constant/fixed
but the column location/sequence/order is fixed. So, instead of selecting/reading the columns by column names/header names, I would like
to read the columns by column number/column locations and import it into exsiting table or drop existing table & create new table.
What you have described is not the way that either SQL or Access operates. Therefore, you have given us an incomplete description. From what object, entity, or file type do you get the original data?
I'm thinking that your better solution involves picking up data in a way that you can use columnar input directly, OR that the import process can dump this to a temporary table that you can use to massage/verify your data and THEN distribute it to where it needs to go in a second step.
This might sound like making work, but you have TWO things to do here - one is import data in a way not obviously based on column names and the other is to assure that it goes to something that IS based on field names. Whether you see that as one or two steps is immaterial. It is a divisible operation, and Julius Caesar's advice - divide and conquer - works in programming, too.
However, there is a direct solution to what you are asking, it is just ugly and actually makes a lot of assumptions about bad context.
For every recordset and every query and every table, there is a collection called Fields that is the enumeration of each field on input. Assuming your recordset name is RS and the recordset is opened and positioned on a valid record, you can use syntax like this:
The Fields collection, like all collections in Access, is zero-based, not one-based, so you start numbering from field 0.
The problem is that in order to be able to do this, you probably already need to know the actual names of the fields beforehand, else how would they become defined so that you could use this method? I'll simply say that being able to do this to a recordset is not quite but almost based on a precondition that the table had already been imported AND you didn't know the field names. This way leads to chaos, which is why I explained it last.
If you cannot agree with the source of the data on a fixed format, you will always have a problem so even though what I am suggesting will get you past the problem and solve the problem the way YOU think it should be solved, it is still a BAD solution.
Link to the spreadsheet or csv file. Specify that the file has no column headers. That will cause Access to treat the column headers as a row of data.
Then you make an append query that selects the data from columns f1, f2, f3, f4 and appends them to the named columns in the permanent table. The problem will be figuring out how to identify which row is the headers. Hopefully, you will have at least one column that is required and that is a number or a date. That will allow you to use criteria similar to:
Where IsDate(f6)
here take a look.
i made a function for you.
if it is true that the columns on your
worksheet is fixed and only changes
the column header names, then
you can use this function
table_name is the name of table to be updated/insert records.
excel_name is the complete path and filename+extension of excel file from
where update is coming.
field1_table1,... is the field names from your table, you can list as many as long
as there is enough column in excel that can accommodate them.
copy and paste the code in Standard Module.
Code:
Public Function fnUpdateFromExcel(ByVal AccessTable As String, _
ByVal ExcelFile As String, _
Sheet As String, _
ParamArray AccessColumns() As Variant)
' arnelgp
' 29-november-2017
'
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strBuilder As String
Dim strColumns As String
Dim i As Integer
Dim CONNECTOR As String
CONNECTOR = "Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database="
' build columns of access table
For i = 0 To UBound(AccessColumns)
strBuilder = strBuilder & AccessColumns(i) & ","
Next
strBuilder = "(" & Left(strBuilder, Len(strBuilder) - 1) & ")"
' open database instance
Set db = CurrentDb
' open excel file as table
Set rs = db.OpenRecordset( _
"SELECT * FROM [" & CONNECTOR & ExcelFile & "].[" & Sheet & "$];", _
dbOpenSnapshot)
' Loop through each recod in excel and append to access table
With rs
If Not (.BOF And .EOF) Then .MoveFirst
Do While Not .EOF
' inset the record to access
strColumns = ""
For i = 0 To UBound(AccessColumns)
strColumns = strColumns & FixSQL(rs(i)) & ","
Next
strColumns = Left(strColumns, Len(strColumns) - 1)
db.Execute _
"INSERT INTO " & AccessTable & " " & _
strBuilder & " " & _
"SELECT " & strColumns & ";"
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Function
Public Function FixSQL(p As Variant) As Variant
Select Case VarType(p)
Case VbVarType.vbNull
FixSQL = "Null"
Case VbVarType.vbString
FixSQL = """" & p & """" 'Chr(34) & p & Chr(34)
Case VbVarType.vbBoolean, VbVarType.vbByte, _
VbVarType.vbCurrency, VbVarType.vbDecimal, _
VbVarType.vbDouble, VbVarType.vbInteger, _
VbVarType.vbLong, VbVarType.vbSingle
FixSQL = p
Case VbVarType.vbDate
FixSQL = "#" & Format(p, "mm/dd/yyyy") & "#"
End Select
End Function
Public Function MyDocuments() As String
MyDocuments = Environ("UserProfile") & "\Documents\"
End Function
Public Function MyDesktop() As String
MyDocuments = Environ("UserProfile") & "\Desktop\"
End Function
