Visual Basic Q&A

Symptoms
When using the Data Access Objects (DAO), Remote Data Objects (RDO) or the ADO GetChunk method, on a long binary field of a Microsoft Access database, binary data may appear corrupt if the binary information previously stored was read from disk using a String (BSTR) variable instead of a Byte array. The problem occurs under one of the following conditions:
You recently upgraded your computer to Windows 98 or Windows NT version 4.0 SP4. Previously stored data now appears corrupt.
-or-You are using a shared database. The corruption occurs when the binary data was stored by clients running on Windows 95 or Windows NT 4.0 SP3, and then extracted by clients running Windows 98 or Windows NT 4.0 SP4 or vice versa.
Resolution
The problem is that the information stored to the database is mistakenly being converted to Unicode before being stored. The “corruption” occurs when reading the information from disk using the Visual Basic or Visual Basic for Applications Get statement.
By design, the Get statement relies on the data type of the variable to determine how the information should be read into Visual Basic. If you pass a String (BSTR) variable to the function, Visual Basic identifies the information as being a text string, and converts it to Unicode. However, since the data is binary and not text, this conversion corrupts the data before it is ever stored to the database.
Although binary data stored in Unicode format can be “decoded” in some cases, it requires that the Unicode symbol table used to map 1-byte ANSI characters to 2-byte Unicode characters be the same for both the conversion to and the conversion from Unicode. Due to the recent addition of the European Currency symbol, the symbol table for Windows 98 and Windows NT 4.0 SP4 is different from those used previously. These changes will have no effect on text strings, but can effect binary data mistakenly converted to Unicode.

Symptoms
The ImportText.exe sample demonstrates various ways to import text files into a Microsoft Access database. There are many ways to import text data to an Access database, and typically the best option is determined by the task requirements.
ADORDODAOFilesysAutomation The sample application attached details the above coding options.
Resolution
The following files are available for download from the Microsoft Download Center:
TextImport.exe(http://download.microsoft.com/download/vb60pro/sample/1/w9xnt4/en-us/textimport.exe)
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591?(http://support.microsoft.com/kb/119591/EN-US/) How to Obtain Microsoft Support Files from Online Services Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

Collapse this tableExpand this table
FileNameSizeImportText.vbp1,464ImportText.vbw56Sample.out3,346Sample.txt3,346Sample_Header.txt3,708Schema.ini422Schema_Header.ini420TextImport.frm28,678TextImport.frx84TextImport.mdb108,544
All files should reside in the same folder. Run the sample application ImportText.vbp and examine the different import/export options. The sample TextImport.mdb is used and should reside in the application path. The default sample text file is Sample.txt. A Sample_Header.txt file is included and contains the column header for the text file. An alternate schema file, Schema_Header.ini, may be used to demonstrate using the ColNameHeader=True option in the schema file corresponding to the Sample_Header.txt file.
Among the data import options demonstrated, DAO is probably the most efficient (fewest layers) or with the smallest memory footprint; especially if importing to an Access database.
Refer to the following list for an overview of the libraries loaded for each data access method. The FileSys objects sample: Scripting Runtime + DAO libraries + Jet librariesThe RDO sample: RDO libraries + ODBC libraries + ODBC Jet library + Jet libraries + Text ISAM driverThe ADO (the default example): ADO libraries (OLEDB + MSDASQL) + ODBC libraries + ODBC Jet library + Jet libraries + Text ISAM driverThe Automation sample: MSOffice Runtime library. For the DAO sample: DAO libraries + Jet libraries + Text ISAM driver The following function is the DAO object sample in the TextImport.vbp application. This code is used in the application when you click the DAO radio button before importing. You can modify the DAO sample by adding a recordset and a loop for data manipulation just as in the FileSys objects example.

Sub DAOOpenTextFileImport()On Error GoTo ErrHandlerlblAction.Caption = “DAO Import…”Dim daoDB As DAO.DatabaseDim strSQL As StringIf chkCreateTbl.Value = 1 ThenDBEngine.IniPath = App.Path & “\Schema_Header.ini”ElseDBEngine.IniPath = App.Path & “\Schema.ini”End IfSet daoDB = OpenDatabase(App.Path, False, False, _”Text;Database=” & App.Path & “;table=” & txtFile.Text)If chkCreateTbl.Value = 1 Then’Use this if you do not already have a table created in Access.’Creates and appends the data in one step.strSQL = “SELECT * INTO [" & txtTable.Text & "] IN ‘” & _App.Path & “\” & txtDatabase.Text & ” ‘”strSQL = strSQL & “FROM ” & txtFile.TextdaoDB.Execute strSQLElse’Delete data before importing – use if necessary.strSQL = “DELETE FROM [" & txtTable.Text & "] IN ‘” & _App.Path & “\” & txtDatabase.Text & “‘”daoDB.Execute strSQL’Append data to Access table.strSQL = “INSERT INTO [" & txtTable.Text & "] IN ‘” & _App.Path & “\” & txtDatabase.Text & “‘”strSQL = strSQL & “SELECT * FROM ” & txtFile.TextdaoDB.Execute strSQLEnd IfGoTo ExitSubErrHandler:lblAction.Caption = “DAO Import – Error.”MsgBox “Error: ” & Err.Number & vbCrLf & Err.DescriptionExitSub:lblAction.Caption = “Complete…”daoDB.CloseSet daoDB = NothingEnd Sub The following function is the FileSys object sample in the TextImport.vbp application. This code is used in the application when you select the FileSys radio button before importing. Notice in the sample code that to create the table layout in Access, based on the Schema_Header.ini file, there is no need to loop through the header file and create the table manually if you use the Text ISAM driver. Although, if you are using the Text ISAM driver then there is no need to use the FileSystemObject (and that is part of the point) unless you must use the FileSystemObject to import, then use DAO and do it in one as shown in the DAO sample code. Since you must use DAO anyway (to create the recordset object) even if you are doing data manipulation on import, then use DAO for the entire process since you already have it loaded in memory to create the recordset.

Private Sub FileSysImport()On Error GoTo ErrHandlerlblAction.Caption = “FileSys Import…”Dim daoDB As DAO.DatabaseDim daoRs As DAO.RecordsetDim fs As FileSystemObjectDim ts As TextStreamDim inLine As VariantDim strSQL As StringDim i As IntegerIf chkCreateTbl.Value = 1 Then’This is an eazy way to create the Table layout in Access based on the Schema_Header.ini file.DBEngine.IniPath = App.Path & “\Schema_Header.ini”Set daoDB = OpenDatabase(App.Path, False, False, “Text;Database=” & App.Path & “;table=” & txtFile.Text)strSQL = “SELECT * INTO [" & txtTable.Text & "] IN ‘” & App.Path & “\” & txtDatabase.Text & ” ‘”strSQL = strSQL & “FROM ” & txtFile.Text & ” WHERE 1=0″daoDB.Execute strSQLSet daoDB = NothingSet daoDB = OpenDatabase(App.Path & “\” & txtDatabase.Text, False, False)ElseDBEngine.IniPath = App.Path & “\Schema.ini”Set daoDB = OpenDatabase(App.Path & “\” & txtDatabase.Text, False, False)strSQL = “DELETE * FROM [" & txtTable.Text & "] IN ‘” & App.Path & “\” & txtDatabase.Text & “‘”daoDB.Execute strSQL, dbFailOnErrorEnd IfstrSQL = “SELECT * FROM [" & txtTable.Text & "] WHERE 1=0″Set daoRs = daoDB.OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)Set fs = New FileSystemObjectSet ts = fs.OpenTextFile(App.Path & “\” & txtFile.Text, ForReading, False, TristateUseDefault)’This skips the column header.If chkColHeader.Value = 1 TheninLine = Split(ts.ReadLine, “,”)End IfWhile Not ts.AtEndOfStreaminLine = Split(ts.ReadLine, “,”)daoRs.AddNewFor i = 0 To UBound(inLine) – 1daoRs.Fields(i).Value = Left(inLine(i), daoRs.Fields(i).Size)Next idaoRs.UpdateWendGoTo ExitSubErrHandler:lblAction.Caption = “FileSys Import – Error.”MsgBox “Error: ” & Err.Number & vbCrLf & Err.DescriptionExitSub:lblAction.Caption = “Complete…”If Not ts Is Nothing Then ts.CloseIf Not daoRs Is Nothing Then daoRs.ClosedaoDB.CloseSet daoRs = NothingSet daoDB = NothingSet ts = NothingSet fs = NothingEnd Sub The simplest example is the Automation sample. A sample TextImport.mdb is used and the example import/export specifications have been created in the sample .mdb file: Sample and sample w/columns. You can find the specification property setting on the Properties tab of the Tab control. To import with or without the column names in the first row create another import/export specification and put the name of that specification in the text box txtSpecName on the tab control. An example specification is included in the sample .mdb file: Sample w/columns. To import the text file with Access Automation you can simply execute the DoCmd.TransferText method of the Access object.

Private Sub AccessAutomateImport()’Assumes table already exists.On Error GoTo ErrHandlerlblAction.Caption = “Access Automation…”Dim AccessApp As access.ApplicationDim strDB As StringstrDB = App.Path & “\” & txtDatabase.TextSet AccessApp = New access.ApplicationAccessApp.OpenCurrentDatabase strDB’To Import with/without Column names in first row create another Import/Export Specification’and put the name of that specification in the Text box ‘txtSpecName’ on the Tab Control.’An example Specification is included in the sample MDB – ‘Sample w/columns’.AccessApp.DoCmd.TransferText acImportDelim, txtSpecName.Text, txtTable.Text, App.Path & “\” & txtFile.TextAccessApp.CloseCurrentDatabaseGoTo ExitSubErrHandler:lblAction.Caption = “Access Automation – Error.”MsgBox “Error: ” & Err.Number & vbCrLf & Err.DescriptionExitSub:lblAction.Caption = “Complete…”appAccess.QuitSet appAccess = NothingEnd Sub For additional details and code refer to the sample application TextImport.exe.

Symptoms
This article describes how to use ActiveX Data Objects Extensibility (ADOX) to create an OLE Object field in a Microsoft Access Database (.mdb file). You must use the adLongVarBinary constant to create the field. You do not have to specify a field size in the field definition.
Resolution
Step-by-Step ExampleCreate a new Standard EXE project in Visual Basic. Form1 is created by default.From the Project menu, click References. From the list of available components, click Microsoft ADO Ext. 2.1 for DDL and Security.Add a CommandButton control to Form1.Paste the following code onto the Declarations section of Form1:

Private Sub Command2_Click()Set cat = New ADOX.CatalogSet tbl = New ADOX.Tablecat.ActiveConnection = _”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\nwind2.mdb;”tbl.Name = “OleObjTable”tbl.Columns.Append “Column1″, adIntegertbl.Columns.Append “Column2″, adIntegertbl.Columns.Append “Column3″, adVarWChar, 50′ Please note adLongVarBinary = 205tbl.Columns.Append “MyOleObject”, adLongVarBinarycat.Tables.Append tblEnd Sub Modify the cat.ActiveConnection assignment to point to a valid Microsoft Access Database file.Run the project, and click Command1. Notice that a table named OleObjTable is created in the database. When you view the table in Design Mode, the Column3 field definition is displayed as OLE Object.

Symptoms
This article describes how to use a Microsoft SQL Server distributed query to retrieve data from a secured Microsoft Access database.
Resolution
Microsoft SQL Server version 7.0 provides you the ability to perform queries against different databases by using OLE DB providers. You query databases by using: OpenQuery or OpenRowset Transact-SQL functions.
-or-
A query with four-part names including a linked server name. To set up a linked server to access a secured Microsoft Access database, use these steps: Configure the registry (by using the Registry Editor) to use the correct Microsoft Access Workgroup Information file (the .mdw file). Use the Registry Editor to add the full path name of the Workgroup Information file to the following registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB Next, set the value to the path and name of the file, such as:

C:\…\MySystem.mdwTo open the registry editor, navigate to the Start button and click Run. In the Run dialog box, type Regedit, and then press OK.In the registry editor, navigate to this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Right-click and select New/String Value.Type SystemDb, and then press Enter.Double-click the SystemDb key in the left window pane.In the Value Data text box, type the full path to your .mdw file.Close the registry editor. Execute the sp_addlinkedserver stored procedure to create the linked server. Specify Microsoft.Jet.OLEDB.4.0 as the provider_name, and specify the full pathname of the Microsoft Access .mdb database file as the data_source. The data_source is evaluated on the server, not the client, and the path must be valid on the server.Execute the sp_addlinkedsrvlogin stored procedure to create login mappings from local logins to Microsoft Access logins.
Steps to Query Secured Microsoft Access DatabaseModify the registry key shown in step 1 of the “More Information” section and add the location of your .mdw file.Start Microsoft Visual Basic 6.0 and select a Standard EXE project. Form1 is created by default.On the Project menu, select References, and set a reference to the Microsoft ActiveX Data Objects 2.1 Library or later.Place two command buttons and a DataGrid control on Form1 (named Command1, Command2 and DataGrid1 respectively).Paste the following code into the Declarations section of Form1:
Note You must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Dim adorst As New ADODB.RecordsetDim adoconn As New ADODB.ConnectionPrivate Sub Command1_Click()Dim strConn As Stringadoconn.Open “Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=master;User Id=<User ID>;Password=<Strong Password>;”adoconn.Execute “EXEC sp_addlinkedserver ‘SecuredJetLS’, ‘Jet 4.0′, ‘Microsoft.Jet.OLEDB.4.0′, ‘c:\…..\MyDatabase.mdb’”adoconn.Execute “EXEC sp_addlinkedsrvlogin ‘SecuredJetLS’, FALSE, NULL, ‘UserName’, ‘Password’”adoconn.CloseMsgBox “Successful Setup”End SubPrivate Sub Command2_Click()Dim SQL As String’ Using OpenQuery syntax.SQL = ” Select a.* from OPENQUERY(SecuredJetLS, ‘Select * from MyTable’) a”‘ Using OpenRowset syntax.’ SQL = “SELECT * From OpenRowset(‘Microsoft.Jet.OLEDB.4.0′,’c:\….\MyDatabase.mdb’; ‘UserName’;'Password’, MyTable)”‘ Using four-part name syntax.’ SQL = “Select * from SecuredJetLS…MyTable”adoconn.CursorLocation = adUseClientadoconn.Open “Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=master;User Id=<User ID>;Password=<Strong Password>;”adorst.Open SQL, adoconn, adOpenStatic, adLockReadOnlySet DataGrid1.DataSource = adorstEnd SubPrivate Sub Form_Load()Command1.Caption = “Setup Linked Server”Command2.Caption = “Query Linked Server”End SubPrivate Sub Form_Unload(Cancel As Integer)adorst.CloseSet adorst = Nothingadoconn.CloseSet adoconn = NothingEnd SubRun the project.Click Setup Linked Server. If you modify sp_addlinkedserver and sp_addlinkedsrvlogin in the connection string with the correct parameters, the linked server is created successfully.Click Query Linked Server. If you modify the connection string and the query text to the correct parameters, the DataGrid control is populated with your data.

Symptoms
ADOX is an extension to ActiveX Data Objects that allows the manipulation of the database schema. This article illustrates how to use ADOX to create a table and add a Primary Key.
Resolution
NOTE: Not all OLE DB providers support the interfaces required to support ADOX methods. With those providers, you have to use Data Definition Queries or another object model to manipulate the database schema.
The first procedure in the example below creates a new table in an existing Microsoft Access database, creates a new field in that table, then creates a primary key index. When adding a single-field primary key, you do not need to use the ADOX Key object.
The second procedure utilizes the ADOX Key object to add a multiple field key to a table.
Steps to Create the Sample Application In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default. On the Project menu, select References to add the following type libraries:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security Add two Command buttons (Command1 and Command2) and the following code to the Form1:

Option ExplicitPrivate Sub Command1_Click()” This code adds a single-field Primary key’Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As ADOX.TableSet Cn = New ADODB.ConnectionSet Cat = New ADOX.CatalogSet objTable = New ADOX.Table’Open the connectionCn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb”‘Open the CatalogSet Cat.ActiveConnection = Cn’Create the tableobjTable.Name = “Test_Table”‘Create and Append a new field to the “Test_Table” Columns CollectionobjTable.Columns.Append “PrimaryKey_Field”, adInteger’Create and Append a new key. Note that we are merely passing’the “PimaryKey_Field” column as the source of the primary key. This’new Key will be Appended to the Keys Collection of “Test_Table”objTable.Keys.Append “PrimaryKey”, adKeyPrimary, “PrimaryKey_Field”‘Append the newly created table to the Tables CollectionCat.Tables.Append objTable’ clean up objectsSet objKey = NothingSet objTable = NothingSet Cat = NothingCn.CloseSet Cn = NothingEnd SubPrivate Sub Command2_Click()” This code adds a multi-field Primary Key’Dim Cn As ADODB.Connection, Cat As ADOX.CatalogDim objTable As ADOX.Table, objKey As ADOX.KeySet Cn = New ADODB.ConnectionSet Cat = New ADOX.CatalogSet objTable = New ADOX.TableSet objKey = New ADOX.KeyCn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb”Set Cat.ActiveConnection = CnobjTable.Name = “Test_Table2″objTable.Columns.Append “PrimaryKey_Field1″, adIntegerobjTable.Columns.Append “PrimaryKey_Field2″, adIntegerobjKey.Name = “PrimaryKey”objKey.Type = adKeyPrimaryobjKey.Columns.Append “PrimaryKey_Field1″objKey.Columns.Append “PrimaryKey_Field2″objTable.Keys.Append objKeyCat.Tables.Append objTable’ clean up objectsSet objKey = NothingSet objTable = NothingSet Cat = NothingCn.CloseSet Cn = NothingEnd Sub NOTE: You might have to adjust the connect string to point to a valid Jet database. Run the application and click the Command buttons. You can check the table definitions for Test_Table and TestTable2 in Microsoft Access 97, Microsoft Access 2000, or the Visual Basic Visual Data Manager add-in.

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.