[Actuarial & Computer Consulting]

MS SQL Server Issues

Visual Basic and Access

Differences in coding directly to SQL compared to Access

Yes/No fields become bit fields in SQL. You can't group by a bit field. While NO is still 0, YES is 1 instead of -1. Best to use 0 and <>0
Use ' to delimit dates, not #
Cannot use [] in field names in a query. Field names cannot have spaces
Cannot include Access functions such as IsNull or IIF
Cannot use TOP or DISTINCTROW in queries
Error handing is different. The ERR variable contains the VB/Access error. You have to examine the Errors collection to get the SQL error to know what is really wrong. ERR/ERROR simply tell you that the ODBC Call failed.
You can use both Jet and ODBC Direct workspaces in the same application
It seems that in a Jet workspace doing a Select on a SQL table after an AddNew while inside a transaction creates locking problems. This does not seem to be a problem using Access tables or in an ODBC Direct workspace. There are ways to deal with this using Transaction Isolation Level (via the SET statement) or control locking as part of the Select statement.

ODBC vs Jet Coding Differences

There are differences in syntax and features between operations in each type of workspace. While this is not an exhaustive list, it is a start in understanding how to convert your Jet code to ODBCDirect. Most of these apply to creating passthrough queries in a Jet workspace as well.

OpenRecordset syntax is different as you must handle lock options when opening the recordset.
rs.Lockedits is not supported in ODBCDirect
Any dbSQLPassThrough queries must be converted to dbExecDirect
FindFirst is not supported in ODBCDirect
Bookmarks and lastmodified are not supported in ODBCDirect
You cannot put a ";" at the end of a query statement
In Jet you use # to delimit a date. In ODBCDirect you use ' (#12/31/97# vs '12/31/97')
In Jet you do Select Top N to return a specific number of records. For ODBCDirect, you must add SET ROWCOUNT N before your query string and SET ROWCOUNT 0 at the end of the query string.
You cannot use [] around field names or table names in ODBCDirect. They shouldn't be necessary since SQL tables and fields cannot have spaces
When you reference a bit field in a Where clause, you cannot simply use the field name. You must specify BitField=0 or BitField<>0

Datasource Issues

There are two basic ways to create a connection to SQL
1. Use a system or user DSN. This requires a registry entry in the client computer but is the most direct way.
2. Build the connect string by referencing the server rather than the DSN. The information to build the string can come from a File DSN.
When you attach a SQL table to Access you must specify either a System or File DSN. If you use a File DSN, Access seems to build the connection string differently. Rather than references the File DSN it uses it to create the connect string - specifying the name of the server and database.
The connect string using a DSN:
"ODBC;DATABASE=dbname;UID=username;PWD=password;DSN=DSNname"
The connect string by accessing the server directly
"ODBC;DRIVER={SQL SERVER};SERVER=servername;DATABASE=databasename;
UID=username;PWD=password;DSN=;"

Basic VB/Access Approaches to Using SQL Data

Continue to use a Jet workspace and SQL tables attached to an Access database. This requires little coding changes. use dbSQLPassThrough to execute stored procedures.
Open a database or connection handle in a Jet workspace directly to the SQL database. Very similar to using attached tables and the better way to go for VB. You still have the overhead of Jet but you also can use syntax that is not supported by SQL Server (FindFirst, TOP N, IIF and other Access functions)
Open a database or connection handle in an ODBCDirect workspace. This eliminates Jet and makes working with SQL Server easier. However, the price is differences in coding. If trying to maintain compatible code, there will often be numerous places where you do one thing if the data is Access and another for SQL data. FindFirst and other functions are not supported. Your query syntax must be dialect specific as Jet will not translate.

ODBC Direct Example - Open Recordset for Editing

Dim wrkSpace as Database
Dim dbData as Database
Dim rs as Recordset

Set wrkSpace = CreateWorkspace("Endorse", "admin", "", dbUseODBC)
Workspaces.Append wrkSpace

sqlDB = utlReadIniFile(App.Path & "\ppacgi.ini", "sql", "Database")
sqlDSN = utlReadIniFile(App.Path & "\ppacgi.ini", "sql", "DSN")
Set dbData = wrkSpace.OpenDatabase(sqlDB, dbDriverNoPrompt, False, "ODBC;DATABASE=" & sqlDB & ";UID=sa;PWD=;DSN=" & sqlDSN)
cSQL = "Select * from UWMaster where Policy = " & CStr(chgset!Policy)
Set rs = dbData.OpenRecordset(cSQL, dbOpenDynamic, dbExecDirect, dbOptimistic)

This assumes you are using a system DSN and that the name of the SQL database and DSN can be found in the INI file.
App.Path only works in VB not Access
You cannot set rs.lockedits under ODBC direct. Handle in OpenRecordset with dbOptimistic

If using JET workspace, can use the normal Access Syntax

Set rs = dbData.OpenRecordset(cSQL, dbOpenDynaset)
rs.LockEdits false

ODBC Direct Example - Calling a Stored Procedure to open a recordset

Dim ws as workspace
Dim rs as Recordset
Dim conn as connection

Set ws = CreateWorkspace("SQL", "admin", "", dbUseODBC)
Workspaces.Append ws
sqlDSN = utlReadIniFile(App.Path & "\premd.ini", "paths", "SQLDSN")
sqldb = utlReadIniFile(App.Path & "\premd.ini", "paths", "SQLDATABASE")
Set conn = ws.OpenConnection("Connection1", dbDriverNoPrompt, False, "ODBC;DATABASE=" & sqldb & ";UID=sa;PWD=;DSN=" & sqlDSN)

conn.QueryTimeout = 0 'make sure process doesn't time out
Set rs = conn.OpenRecordset("MakePremiumDetail", dbOpenSnapshot, dbExecDirect)

Again, this assumes you are using a System DSN and have entries in an INI file
This recordset cannot be edited - it is a snapshot. I don't know if you can open a recordset that can be edited using a stored procedure.
In this example, the stored procedure begins returning records and the program can continue processing before the procedure has completed running. This is because the stored procedure does not have any group by or order by clauses so once it has a record it starts to send it.
If using a JET workspace, then use dbSQLPassthrough. If sending parameters put following the procedure name and separate parameters by commas
- ```
cSQL = "lastsearch '" & cLName & "'"
Set rs = dbData.OpenRecordset(cSQL, dbOpenSnapshot, dbSQLPassThrough)
```

ODBC Direct Example - Calling a Stored Procedure using Execute

Assuming you have a connection variable, conn, in a JET workspace, you can call a stored procedure using Execute to perform an action - as opposed to getting back records.

cSQL = "Update Master SET CancelFlag = 'Y'"
conn.Execute cSQL, dbExecDirect

You can run the procedure asynchronously by using dbRunAsync (dbExecDirect + dbRunAsync)
If using a database handle in ODBC, you must use the Connection property: db.Connection.Execute
To execute a stored procedure in a Jet workspace use
- ```
db.Execute cSQL, dbSQLPassThrough
```

Adding Records in SQL Tables with Identity Columns

In an ODBC Direct workspace, you can open a recordset for a table with an identity (called Autonumber or Counter in Access) column and add a new record. Since you cannot use lastmodified to return to the correct record, you have to move to the record by some other means. In this example, the cSQL statement will not return any records.

Set rs = db.OpenRecordset(cSQL, dbOpenDynamic, dbExecDirect, dbOptimistic)

rs.AddNew
rs!UserName = cUsername
rs!StartDate = Date
rs!UnverifiedFlag = bUnverify
rs!Inuse = cUsername
rs.Update
rs.MoveFirst
BNum = rs!BatchNumber
getBatchNumber = BNum

In a JET workspace, you must use the dbSeeChanges option. Unlike ODBC Direct, you can use lastmodified to locate the record you added. You also need the dbSeeChanges if doing an Execute to perform an Update query.

Set rs = dbPmtData.OpenRecordset(cSQL, dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs("StartDate") = dStart 'Field default is Date but we're setting it explicitly
rs("Username") = cUserName
rs("UnverifiedFlag") = (cBatchType = "Unverified")
rs("Inuse") = cUserName
On Error GoTo InitBatchErr
rs.Update
rs.Bookmark = rs.LastModified
lBatchNumber = rs("BatchNumber")

cSQL = "Update InvBatch "
cSQL = cSQL & "Set Inuse="""" "
cSQL = cSQL & "Where BatchNumber=" & CStr(lBatchNumber) & ";"
dbPmtData.Execute cSQL, dbSeeChanges

ODBC Direct Example using QueryDefs

With a connection or database variable, you can define a querydef to call a stored procedure. With this approach, you can have output parameters in the stored procedure and a way to return them to VB. One way to use this approach is to have the stored procedure return the error code, if any.

Set qd = conn.CreateQueryDef("")
qd.SQL = "{Call MakeDecCancelsActive(?, ?)}" 'the ? holds the place of the parameter
qd.ODBCTimeout = 0 'make sure query doesn't time out
qd.Parameters(0).Direction = dbParamInput
qd.Parameters(1).Direction = dbParamOutput
qd.Parameters(0) = iPolicy
qd.Execute dbRunAsync 'or you can set a recordset from the querydef: set rs = db.OpenRecordset
Do While qd.StillExecuting
    'do stuff while query is executing
    DoEvents
Loop
p = qd.Parameters(1) 'to access the returned parameter

Other SQL Examples using ODBC workspace

The following examples use dbsql - a database handle in an ODBC workspace. They could work just as well with a connection. The example assumes that ppadata is the name of the database.
To empty a table, it is better to use Truncate Table than a Delete query as the former does not create any log entries
- ```
dbsql.Execute "Truncate Table " & t1, dbExecDirect
```

To dump the transaction log for a database

dbsql.Execute "dump transaction ppadata with no_log", dbExecDirect

Example Illustrating differences between ODBCDirect and Jet with Transactions

With Jet, if you add a record to a table and then query from that table, you will timeout. This can be done, however, in ODBCDirect. With the following code, set bUseODBC to determine how you want the code to run. This example also shows coding differences. Note that either approach will work without the transaction.

Dim d As Date, bUseODBC As Boolean
d = #12/31/97#
Randomize
bUseODBC = True

If bUseODBC Then
	Set ws = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append ws
	Set db = ws.OpenDatabase("PPADATA", dbDriverNoPrompt, False, cConnection)
Else
	Set ws = CreateWorkspace("", "admin", "", dbUseJet)
	Set db = ws.OpenDatabase("", dbDriverNoPrompt, False, _
		"ODBC;DATABASE=ppadata;UID=sa;PWD=;DSN=PPADATAsql")
End If

ws.BeginTrans
If bUseODBC Then
	Set rs = db.OpenRecordset("Select * from CtrlPrint where Policy=0", _
		dbOpenDynamic, dbExecDirect, dbOptimistic)
Else
	Set rs = db.OpenRecordset("Select * from CtrlPrint where Policy=0", dbOpenDynaset)
End If
rs.AddNew
rs!Policy = 100000
rs!Expiration = d
rs!Revision = 0
rs!ItemNumber = CInt(Rnd * 100)
rs!PrintJob = "TEST"
rs.Update
rs.Close

If bUseODBC Then
	Set rs = db.OpenRecordset("Select * from CtrlPrint where Policy = 100000", _
		dbOpenDynamic, dbExecDirect, dbOptimistic)
Else
	db.QueryTimeout = 5 'set so won't take so long when timing out
	'this will time out regardless of what Policy is used
	'if add dbSQLPassThrough option the query will not timeout. 
	'However, you can't edit the recordset below
	Set rs = db.OpenRecordset("Select * from CtrlPrint where Policy = 100000", dbOpenDynaset)
End If
While Not rs.EOF
	rs.Edit
	rs.Update
	Debug.Print rs!PrintJob
	rs.MoveNext
Wend
rs.Close

ws.CommitTrans
ws.Close

Note that this represents my current understanding of programming in VB using Microsoft SQL. Use this advice with caution. I do not assume any responsibility for problems you encounter using these ideas.