ODBC Microsoft Access Driver Error - too few parameters expected 1; Access Table Fields Renamed

[TyJr303]

I need to know how I can update the SQL Command in an excel spreadsheet that is connected to an Access Query where data is refreshed in order to change the names of the fields that is being pulled from the Access Query to match after they were changed. We recently changed the names of the table fields in the Query so I am getting the error message of "too few parameters expected 1". Is there code I can add in the Excel SQL to replace the field names in the mapping to match the new field names in the query so it knows where to pull the data from?

 

[Fazza]

Maybe. The exact syntax will depend on your Excel version.

General idea - that might not be perfect though, depending on the compexity of the queries - might be a simple text replacement in the SQL. So replace old field name by new field name. Be best to back up your file beforehand.

So in Excel 2003, it might be just like below, untested

hth

const strFieldNameOld as string = "oldname"
const strFieldNameNew as string = "newname"

dim qt as querytable
dim wks as worksheet

for each wks in worksheets
for each qt in wks.querytables
debug.print vbcr & "before on worksheet " wks.name & vbcr & qt.sql 'to see what it looks like before
qt.sql=replace$(qt.sql,strfieldnameold,strfieldnamenew)
debug.print vbcr & "after" & vbcr & qt.sql & vbcr 'to see what it looks like after
qt.refresh'may as well refresh it while we're here
next qt
next wks
set qt = nothing
set wks=nothing

 

[TyJr303]

Maybe. The exact syntax will depend on your Excel version.

General idea - that might not be perfect though, depending on the compexity of the queries - might be a simple text replacement in the SQL. So replace old field name by new field name. Be best to back up your file beforehand.

So in Excel 2003, it might be just like below, untested

hth

const strFieldNameOld as string = "oldname"
const strFieldNameNew as string = "newname"

dim qt as querytable
dim wks as worksheet

for each wks in worksheets
for each qt in wks.querytables
debug.print vbcr & "before on worksheet " wks.name & vbcr & qt.sql 'to see what it looks like before
qt.sql=replace$(qt.sql,strfieldnameold,strfieldnamenew)
debug.print vbcr & "after" & vbcr & qt.sql & vbcr 'to see what it looks like after
qt.refresh'may as well refresh it while we're here
next qt
next wks
set qt = nothing
set wks=nothing

Thank you for the Info. Let's say I have a basic query that is pulling employee names and I am updating it through the embedded excel file. If we change the name of the table field in access from "Name" to "EmployeeName" how can I update the field name in my excel Sql command with code based on the below:
SELECT qryEmployeeDepartment.EmployeeName
FROM `DATABASE\db1`.qryEmployeeDepartment qryEmployeeDepartment

I need to know the code to do the change instead of manually as this is just a basic example and i will need to do this for very large databases with lots of field updates.