Query with vb functions passed to access database object (1 Viewer)

[Mr Banan Qry]

Hi!

I'm working on a project where users are interacting with an access database through a collection of userforms in Excel.

I've got an issue with a query that I try to run from a sub routine in excel. The query works fine when i runnit i access but when i try to execute it through a database object in excel(through vba) i receive a runtime error. The error states that there is an undefined function in the expression.
The aim of this query is to append all customers along with a datevalue (selected from a userform) to a table (provided that the record doesn't exist already). The table receiving the records holds customer, date and price.

i've encountered this issue or similar issues several times and would like to find a better solution to it. I feel all my ideas are a bit retarded and imagine that someone here has a way more elegant solution.

The ideas that i have thought of this far is
1) Create a temp table, delete old values, load new values.
Then the date values are available to use in a table and i have no trouble to create the append query
2) Create a VBA procedure where i iterate through the records valid for the date and compare with all customers and then append the ones thar are missing.
3) Create a macro which runs the SQL expression and then create an access application object and run the macro (using DoCmd.RunMacro) through excel (however this would require that all the users has a license for ms access)

Btw i also feel my SQL expression is a bit retarded. First of i tried setting using only a string in the second join criteria but that didn't work. However I did manage to get to it work by concatenate the field with a string and erase the field value.

INSERT INTO MontlyResultValues ( CustomerName, ValidForMonth ) 

SELECT CustomerToSAPID.CustumerName, #9/1/2010# AS DateForInput 

FROM CustomerToSAPID LEFT JOIN MontlyResultValues ON
CustomerToSAPID.CustumerName 
= MontlyResultValues.CustomerName 
And
 Cstr(Replace(CustomerToSAPID.CustumerName,CustomerToSAPID.CustumerName, '') & '2010-09-01')  
= Cstr(format(MontlyResultValues.ValidForMonth,'YYYY-MM-DD')) 

WHERE (((MontlyResultValues.CustomerName) Is Null))

the vb code where i receive the runtime error

Private Sub ExampleSub()

Dim strSqlCommandText As String
Dim pubDatabaseForPfMng As Database
Set pubDatabaseForPfMng = OpenDatabase("C:\Documents and Settings\" & Get_User_Name & "\Desktop\Example.mdb", False, False, "MS Access;PWD=" & strAccDbPassW & "")

strSqlCommandText = "INSERT INTO MontlyResultValues ( CustomerName, ValidForMonth ) SELECT CustomerToSAPID.CustumerName, #9/1/2010# AS DateForInput FROM CustomerToSAPID LEFT JOIN MontlyResultValues ON CustomerToSAPID.CustumerName = MontlyResultValues.CustomerName And Cstr(Replace(CustomerToSAPID.CustumerName,CustomerToSAPID.CustumerName, '') & '2010-09-01')  = Cstr(format(MontlyResultValues.ValidForMonth,'YYYY-MM-DD')) WHERE (((MontlyResultValues.CustomerName) Is Null))"
pubDatabaseForPfMng.Execute strSqlCommandText, dbFailOnError

end sub

 

[Pat Hartman]

I'm not sure why you would build the FE in Excel, it adds a layer of complexity that you wouldn't have if the forms were in Access. You could try copying the UDF into the Excel code module. If you are running the query in Excel, that's where it would need to be.