VBA + SQL statement creation/execution.

[Playerpawn]

I have a form with a lot of unbound fields. I chose this method because I need a lot of control over the form. I have completed everything I need, seemingly, except writing the information to the database.

I have declared a string variable to dynamically generate my INSERT sql statement then wish to execute it.

I am having two issues:

1) a string can only be 256 characters... what data type can I use for a much longer statement?



2) how do i execute my string (or whatever new datatype you suggest) against the database and have it return an error code so I can be sure it happened; for example, just using CurrentDB.Execute(sql) does not tell me if anything went wrong

Thanks.

[ketbdnetbp]

You stated: 1) a string can only be 256 characters?

Question: How long is the sql string you refer to in your post?

I seem to recall using strings in VBA that exceeded the 256 number.

Regarding the second part of your post, if you want something simple, you could try something like:

dim x as Integer
dim y as Integer
x= DCount("*","yourtablename") ‘run before sql execution

‘execute sql here

y= DCount("*","yourtablename") ‘run after sql execution

If y > x then
MsgBox “Operation successful”
Else
MsgBox “Error” 'Then, handle error.
End if

All the best !

Jim

[ssanfu]

I am having two issues:

1) a string can only be 256 characters... what data type can I use for a much longer statement?

A TEXT field in Access is 255 characters. This is not the same as a variable in VBA declared as a string.

There are two kinds of strings in VBA: variable-length and fixed-length strings.

• A variable-length string can contain up to approximately 2 billion (2^31) characters.
• A fixed-length string can contain 1 to approximately 64K (2^16) characters.

This should be large enough for your purposes...

2) how do i execute my string (or whatever new datatype you suggest) against the database and have it return an error code so I can be sure it happened; for example, just using CurrentDB.Execute(sql) does not tell me if anything went wrong

Use:

CurrentDb.Execute SQL, dbFailOnError

From VBA Help:

In a Microsoft Jet workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.

In earlier versions of the Microsoft Jet Database Engine, SQL statements were automatically embedded in implicit transactions. If part of a statement executed with dbFailOnError failed, the entire statement would be rolled back. To improve performance, these implicit transactions were removed starting with version 3.5. If you are updating older DAO code, be sure to consider using explicit transactions around Execute statements.

For best performance in a Microsoft Jet workspace, especially in a multiuser environment, nest the Execute method inside a transaction. Use the BeginTrans method on the current Workspace object, then use the Execute method, and complete the transaction by using the CommitTrans method on the Workspace. This saves changes on disk and frees any locks placed while the query is running.

[Playerpawn]

Two more questions and you'll save my azz.

1) Is there a way to instead of showing Access's error messages on DB fail to show my own? Can I put some function's result into a variable and display my own errors?

2) How can I put a bunch of unbound textbox's values into a string for inserting? When I do the following, I get various errors:

dim sql_string

sql_string = "INSERT INTO tbl_mytable VALUES ('" & Forms("myform").Controls("firstitem").Value & "')"

And remember, don't pick my SQL apart yet, I'm just trying to make the string... except I would have a lot more controls to add to the list of VALUES.

Also, I don't need to name the fields if I'm going to insert a value for every one, correct?

Thanks.