Visual Basic Q&A

Symptoms
In Access, when you first link (attach) an external table using an ODBCdriver, you have the option to store the User ID and password for the tablelocally. If you do not store the ID and password locally, you will beprompted later for such information when you open the table.
This article demonstrates how to bypass the Login prompt when you open anAccess linked table by pre-connecting to the database and providing User IDand password programmatically in Basic.
Resolution
The Microsoft Jet database engine caches authentication information foreach DSN. This prevents users from being prompted to login to remotedatabases each time a table is opened. You can take advantage of thisbehavior by pre-connecting to the database directly and programmaticallyproviding user ID and password to prevent the login prompt from appearingwhen opening linked tables that don’t have the user ID and password cached.In Access, create a new database, db1.mdb, and a linked table,dbo_authors, from SQL Server Pubs database.In Visual Basic, start a new project and choose “Standard EXE.” Form1is created by default.In Access, create a new database and create a new form (Form1).In Visual Basic 4.0 and later, add a Reference to:

Microsoft Data Access Object 2.x(VB4 16-bit)Microsoft Data Access Object 3.x(VB4 32-bit; VB5) Paste the following code in the General Declarations section of Form1:
Note You must change Username=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

Sub Command1_Click()Dim db1 As DatabaseDim db2 As DatabaseDim rs As RecordsetDim strConnect As String’*** You have to modify the path to where db1.mdb is locatedSet db1 = OpenDatabase(“C:\MyTest\db1.mdb”)strConnect = UCase(db1.TableDefs(“dbo_authors”).Connect) & _”;Username=<username>;PWD=<strong password>”Set db2 = OpenDatabase(“”, False, False, strConnect)db2.CloseSet db2 = NothingSet rs = db1.OpenRecordset(“dbo_authors”)Debug.Print rs(0)Debug.Print “Recordset Opened Successfully”rs.Closedb1.CloseSet rs = NothingSet db1 = NothingEnd Sub NOTES:
You must provide correct login information, User ID and Password, instrConnect to establish the connection.If you know which DSN the table is linked to, you can hard-code thevalue of strConnect.The Microsoft Jet database engine will first try to log you in with thesame user ID and password that you log into the Jet database with(default is Admin/no password). If you make the local login match theserver login, you will not get any login prompts.Microsoft SQL Server can integrate its security mechanism with MicrosoftNT domain accounts. If the user has a valid account in the domain, youwill not get any login prompts.

Symptoms
Microsoft Visual Basic crashes when appending a newly-created column to a table by setting the ParentCatalog property of a column to an active catalog and adding it to an existing table.
Resolution
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
This problem has been corrected in MDAC 2.5 and later.

Symptoms
When you attempt to add a new record to a recordset, the Data Access Objects (DAO) Recordset Update method may fail silently when the following conditions are met: The Recordset is based on a query that joins two tables.The table being updated has a Required field enforced by a Primary Key or Unique Index.No values are supplied for any of the fields in the Recordset.
Resolution
To work around this problem, use either of the following methods: If the joined table is unnecessary, remove the joined table from the query. Doing this causes the 3314 error to fire as expected. To remove the joined table:
Open the database in Microsoft Access.Open the query in Design View.Delete the unnecessary table from the query.Save and close the query.Prior to calling the Update method, write code that checks to ensure that a value is supplied for all fields in the table where the Required Value property is set to Yes. For example, if you are using text boxes on a form for data entry and you have a text box named txtSSN and a required field in the table named SSN, check the Text property of the txtSSN text box prior to calling the Update method to ensure it contains a value as follows:

If Trim(txtSSN.Text) = “” ThenMsgBox “The SSN field is Required. You must supply a valid SSN.”txtSSN.SetFocusExit Sub End If Prior to calling the Update method, write code that checks to ensure that a value is supplied for all fields in the table where the Required Value property is set to Yes. For example, if you are using text boxes on a form for data entry and you have a text box named txtSSN and a required field in the table named SSN, check the Text property of the txtSSN text box prior to calling the Update method to ensure it contains a value as follows:

If Trim(txtSSN.Text) = “” ThenMsgBox “The SSN field is Required. You must supply a valid SSN.”txtSSN.SetFocusExit Sub End If

Symptoms
A Visual Basic for Applications procedure that uses ODBCDirect to createand populate a table in another Microsoft Access database also createscorrupted unique indexes in that table. This behavior occurs if theprocedure performs the following steps in sequence:BeginTrans on the ODBCDirect workspaceExecute method on a connection to Create Table with Unique IndexesExecute method on the connection to Append records to that tableExecute method on the connection to Update the newly added recordsCommitTrans on the ODBCDirect workspaceIf you modify data in a field that is a unique index in the resulting tablebut is not a primary key, you receive the following error message when youtry to commit the record:

Reserved Error (-1601); there is no message for this error.You are able to make changes to data in other fields.
This behavior occurs when the ODBC data source is a Microsoft Access 97database or a Microsoft Access version 7.0 database.
This behavior also occurs if you use Remote Data Object (RDO) instead ofODBCDirect in the Enterprise Edition of Microsoft Visual Basic version 4.0.
This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout Visual Basic for Applications, please refer to the “BuildingApplications with Microsoft Access 97″ manual.
Resolution
If you have already created the table and are unable to enter data becauseunique indexes are corrupted, press ESC to cancel the changes to therecord. Then compact the database. After you have compacted the database,you can make changes to the data in the unique index fields.
There are three methods to prevent the corruption from occurring.
Method 1The recommended resolution is to use a Microsoft Jet workspace rather thanan ODBCDirect workspace to create and modify the table. When you use anODBCDirect workspace to modify a Microsoft Jet database, data accessobjects (DAO) loads the Microsoft Jet ODBC driver, which in turn loadsMicrosoft Jet. A procedure in which DAO creates and modifies the tabledirectly without going through ODBC is more efficient.
Method 2Commit the transaction after you have created the table but before youappend the records, or after you have appended the records but before youmodify them.
Method 3If you must create the table, append the records and modify the recordswithin a single transaction, create the unique indexes by executing an SQLstatement that is separate from the SQL statement that creates the table.Note that you can create a primary key in the same SQL statement thatcreates the table; although other unique indexes become corrupted, aprimary index does not become corrupted.
The following example contains a procedure that creates the table, appendsthe records, and modifies the records within a single transaction.Create a blank database in Microsoft Access version 7.0 or 97.Create an ODBC DSN whose data source is the database created in Step 1, and name it “TestRDO97″ (without the quotation marks).Create a blank database in Microsoft Access 97.Create a module and type the following line in the Declarationssection if it is not already there:

Option Explicit Type the following procedure:

Sub CreateIndexTrans (strTableName as string, strDSN as string)Dim Connection1 As Connection, ws As WorkspaceOn Error GoTo ErrorhandlerSet ws = DBEngine.CreateWorkspace _(“TransAct”, “Admin”, “”, dbUseODBC)Set Connection1 = ws.OpenConnection(“Con1″, _dbDriverCompleteRequired, , “ODBC;DSN=” & strDSN)ws.BeginTransConnection1.Execute “CREATE TABLE ” & strTableName & _” (ID INTEGER constraint ID PRIMARY KEY, ” & _”LastName Text (50), FirstName Text (50), keyCode ” & _”Text (10), SSN Text (20))”‘———– Create Indexes ——————Connection1.Execute “CREATE INDEX ” & _”idxLastName ON ” & strTableName & ” (LastName)”Connection1.Execute “CREATE UNIQUE INDEX ” & _”idxKeyCode ON ” & strTableName & ” (KeyCode)”Connection1.Execute “CREATE UNIQUE INDEX ” & _”idxSSN ON ” & strTableName & ” (SSN)”‘———————————————Connection1.Execute “INSERT INTO ” & strTableName & _” (ID, FirstName, LastName, KeyCode, SSN) ” & _”Values (1, ‘Bob’, ‘Wire’, ‘ABC’,'012-34-5678′)”Connection1.Execute “UPDATE ” & strTableName & _” SET FirstName = ‘Robert’, LastName=’Wires’, ” & _”KeyCode=’A1B1C1′, SSN=’987-65-4321′ WHERE ID = 1″ws.CommitTransConnection1.CloseMsgBox strTableName & ” created in other database.”Exit_CreateIndexProblem:Exit SubErrorhandler:MsgBox CStr(Err) & ” ” & Err.DescriptionResume Exit_CreateIndexProblemEnd Sub To run this subroutine, type the following line in the Debug window,and then press ENTER:
CreateIndexTrans “tblIndexTrans”, “TestRDO97″Open the database you created in Step 1.Open tblIndexTrans.Change the data in the keyCode field or in the SSN field. Note that you can successfully commit the record.