UDFs that accept arguments to process UPDATE/INSERT INTO SQL stmts?

[steve728]

Before attempting to create a User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?

 

[steve728]

I should have added that I need this kind of a UDF written in VBA code. My version of MS Access is 2003.

Steve G.

 

[MichaelRed]

it is more common to write a class module to interface to 'foregin' database types. It is, perhaps, a bit more work to start with, but permits a lot more flexability. A Class module to interface to the foregin database would need to be able to do the three common record functions (add, edit and delete) as well as their related processes (locate/retrieve a specific record, edit the content of specific field(s), restore the record to the database, etc.). I have a set of routines for a Pervasive Flat file database structure and could share a few of hte smaller ones as examples.

In the general sense, the interface to most databases should be based on the SQL standard of the foregin db, id it syupports SQL at all. The only ratioale I wuold see as pratical for a class / udf type of interface would be for a system which does NOT supprt any reasonable version of SQL. Even in many of these, I would opt for use of temporary table(s), where the actual manipulation was restricted to retrievial & return of the record(s) of immediate interest and all operations were via standard SQL/recordset operations. Unless you are involved in large recordset(s) with multiple users and frentic change activity such an approach should be as safe as the udf OR class module approach and a WHOLE lot easier to develop. If their are likely to be conflicts in the db changes by different users, these will exist in any of these approaches and will require extensive error/conflict checking throughout your processing.




MichaelRed