Visual Basic Q&A

Symptoms
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
Resolution
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe(http://download.microsoft.com/download/excel2000/sample/12_27_2000/w98nt42kme/en-us/excelado.exe)Release Date: December 12, 2000
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 ServicesMicrosoft 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.Why Use ADO?The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel: Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format ismaintained. If you require “conditional” formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.Jet OLE DB Provider Specifics for Excel WorkbooksThe Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks: Excel 3.0Excel 4.0Excel 5.0Excel 8.0NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:

Dim oConn As New ADODB.ConnectionWith oConn.Provider = “Microsoft.Jet.OLEDB.4.0″.Properties(“Extended Properties”).Value = “Excel 8.0″.Open “C:\Book1.xls”‘…..CloseEnd With ?????-or-

Dim oConn As New ADODB.ConnectionoConn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=C:\Book1.xls;” & _”Extended Properties=”"Excel 8.0;”"”oConn.Close Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.

oRS.Open “Select * from [Sheet1$]“, oConn, adOpenStatic Use a range with a defined name (for example, [Table1]).

oRS.Open “Select * from Table1″, oConn, adOpenStatic Use a range with a specific address (for example, [Sheet1$A1:B10]).

oRS.Open “Select * from [Sheet1$A1:B10]“, oConn, adOpenStatic Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to “guess” the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.Files Included with the SampleThe ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project FilesExcelADO.vbpForm1.frmForm1.frxActive Server PagesEmpData.aspOrders.aspMicrosoft Excel WorkbooksOrdersTemplate.xlsEmpDataTemplate.xlsProductsTemplate.xlsSourceData.xlsMicrosoft Access DatabaseData.mdbHow to Use the SampleExtract the contents of the .exe file to a folder.
To use the Visual Basic project: In Visual Basic, open the ExcelADO.vbp file. On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.Press the F5 key to run the program. A form for the demonstration appears.Click Sample 1.This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:On the Insert menu in Excel, select Names, and then select Define. In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel’s OFFSET function, to compute a total on the data added to the worksheet. Quit Microsoft Excel and return to the Visual Basic application.Click Sample 2.This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3.This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new “Products” worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any “conditional” formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4.This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet (“Sheet1$” or “Sheet2$”) for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.To use the Active Server Pages (ASP):Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.Copy the following files to the folder you created in the previous step:EmpData.aspOrders.aspData.mdbEmpDataTemplate.xlsOrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application. (c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

Symptoms
CnPool.exe contains a sample Visual Basic project that is used to demonstrate how Connection Pooling and Prepared Statements affect the scope and duration of temporary objects within Tempdb. This sample project might be useful in understanding the scope and longevity of temporary objects with Connection Pooling and/or Prepared Statements enabled.
When connection pooling is enabled, any temporary objects that the client creates will remain until the ODBC driver deletes them or the connection is closed, and SQL Server resets all connection specific state variables.
Any temporary object a component creates in SQL Server belongs to the connection, not the component. Consequently, when the component terminates, connection pooling disconnects from SQL Server but does not close the connection. The connection remains in the connection pool and is issued to the next component as needed. Because the connection is not released, the state information is preserved and the temporary objects are visible to the next component using the connection. For example, if you create a temporary table with a component and then terminate the component, when the next component using the same pooled connection attempts to create a temporary table with the same name, the attempt fails because the temporary table already exists for that scope.
This behavior is by design.
Resolution
The following file is available for download from the Microsoft Download Center:
Cnpool.exe(http://download.microsoft.com/download/sql65ent/sample/1/w9xnt4/en-us/cnpool.exe)
Release Date: JUN-10-1998
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.
How to Run the Sample Application Before running the sample application you must register ODBCw32.dll using the Regsvr32.exe utility. To do this, click Run on the Start menu. When the Run dialog box appears, type the following command and click OK:

regsvr32 <path to odbcw32.dll>\odbcw32.dll To examine Connection Pooling in more detail, open the code sample project in Visual Basic design mode. If you get an error when you attempt to run the Visual Basic project in design mode, reselect the reference to ODBCw32.dll (Project-References).
NOTE: The code sample depends on the Pubs database by default. Make the appropriate changes in the connection string for your specific environment.
Before running the Visual Basic application, run the SQL Client utility ISQL/w. In ISQL/w, change the database to Tempdb and type the following in the query command window:

sp_who2select * from sysobjects order by name
Enable Connection Pooling and Disable Prepared Statements Now you are ready to run the code sample.
Select the Enabled option in the Connection Pooling pane.Select the Disabled option button in the Prepared Stmt pane.In the Command text box, type a statement to create a temporary table. For example, type the following:

SELECT * INTO #temp FROM Employee Execute the query text in the ISQL/w query window. Notice that NO connection labeled “ODBC ConnPool” is listed.In the Visual Basic sample application, click Open Connection and then click Execute Command.Rerun the query in the ISQL/w query window. You should now see one connection labeled “ODBC ConnPool” and a #temp table in the listing below.In Visual Basic, click Close Connection and rerun the ISQL/w query. You should see that the “ODBC ConnPool” connection did not disappear and the #temp table is still listed below. This is because Connection Pooling is enabled and although the connection was closed with Visual Basic, it remains active in the Connection Pool until Visual Basic is closed.
Disable Connection Pooling and Disable Prepared StatementsClose Visual Basic and reopen it with the same sample application as in the previous procedure.Run the Visual Basic sample application and this time do not click Enabled in the Connection Pooling pane. Also, make sure that the Disabled option is selected in the Prepared Stmt pane.Type “SELECT * INTO #temp FROM Employee” (without the quotation marks) in the Command text box.Rerun the query in the ISQL/w window and notice that there is no ODBC ConnPool connection listed and the #temp table is not listed below.In Visual Basic, click Open Connection and then click Execute Command.Rerun the ISQL/w query. You will notice that the ODBC ConnPool connection is listed and the #temp table is listed below.In Visual Basic, click Close Connection and rerun the ISQL/w query. You will notice that the ODBC ConnPool connection and the #temp table are gone. This demonstrates that no connections have been pooled and when you close the connection, they are actually closed.
Enable Connection Pooling and Enable Prepared StatementsClose Visual Basic and reopen the sample application.Enable both Connection Pooling and Prepared Stmt and then run the previous statement (SELECT * INTO #temp FROM Employee) by clicking Open Connection and then clicking Execute Command.Before clicking Close Connection in Visual Basic, rerun the ISQL/w query and you will notice that the ODBC ConnPool connection exists (as before), but a temporary stored procedure, #odbc#____xxxx, is listed below. This is because the statement executed was actually wrapped in a stored procedure which was created in the Tempdb and executed.In Visual Basic, click Close Connection and rerun the ISLQ_w query. Notice that the ODBC ConnPool connection remains but the #odbc# reference is gone, unlike the #temp table earlier.
Because we use a prepared statement, the #temp table is created within the scope of a stored procedure and, though Connection Pooling is enabled, the stored procedure goes out of scope and is dropped when the connection is closed in Visual Basic. Because the #temp table created by the stored procedure only exists within the scope and duration of the stored procedure, it is also dropped. For additional information, please see the following article in the Microsoft Knowledge Base:
151536?(http://support.microsoft.com/kb/151536/EN-US/) INF: SQLPrepare and Temporary Stored Procedures in SQL Server You can repeat the previous steps and experiment with different combinations of Connection Pooling and Prepared Statements. If you first execute the statement as a prepared statement and then you open a new connection (without closing the original connection) and execute the statement as a non-prepared statement, another connection is created. The #odbc# object disappears but the #temp object does not.
Conclusion In conclusion, you can avoid some overlapping in the Tempdb that occurs during the creation of temporary tables and other state variables when Connection Pooling is enabled, if you execute the statements within a stored procedure or if you execute prepared statements, for example, ADO - Command.Prepared = True. You can trap the error in code when you attempt to create the temporary table as a prepared statement if you open a new connection (without closing the current connection) and execute the statement. Note that another connection is opened and the connection that caused the temporary object conflict is out of scope for this connection, and the statement should execute normally. Otherwise, you can wait for the default connection timeout of 60 seconds and try again.
Experiment with Connection Pooling with the sample application to gain a better understanding of how Connection Pooling affects the Tempdb environment and how you can avoid overlapping results.

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 sample demonstrates how a Visual Basic 4.0 application calls Fortranand Visual C DLL’s. Specifically, it shows how to pass fixed lengthstrings to and from those DLL’s.
Resolution
The following file is available for download from the Microsoft Download Center:
Vbstring.exe(http://download.microsoft.com/download/vb40ent/sample40/1/w9xnt4/en-us/vbstring.exe)Release Date: Jan-01-1997
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 ServicesMicrosoft 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.
Visual Basic 4.0 replaced the string management system used by previousversions of Visual Basic with a more robust string management system.Visual Basic 4.0 relies on the OLE automation data types, such as BSTR andsafe arrays, to manage string usage. However, passing arrays of numerictypes, integers, or reals were not changed. For instance, before passing anarray of strings to a DLL, the strings must be converted to an array ofbytes, and then converted back to strings on return from the DLL. Please,refer to VB4DLL.TXT that ships with Visual Basic 4.0 for more information.
All new C DLL’s should use the syntax described in the VB4DLL.TXT filebecause the API calls that are employed allow for improved error handling.The programmer can do something in the event of an error, such as display amessage box and abort the operation.
Please note that the new Fortran DLL’s cannot use the syntax described inVB4DLL.TXT because they have no direct access to the correct APIfunctions.
The sample also demonstrates different methods you can use to resolve thefunction symbol within the DLL. A mismatch in the function symbol resultsin a Visual Basic runtime error, “Specified DLL function not found (Error453).” The sample includes two methods to resolve C function symbols andthree methods to resolve Fortran function symbols.
Each source file contains more information pertinent to that language.
This sample also enables you to choose whether the data is passed to eitherthe C or Fortran DLL.
This sample shows how to pass the following:
Two dimensional array of 4-byte integers.Two dimensional array of 8-byte floating point numbers.String.One dimensional array of strings.Two dimensional array of strings.One dimensional array of 4-byte integers, a one dimensional array of8-byte floating point numbers, and a one dimensional array of strings.
Sample Files

FileNameDescription——————————————————-cdll.cC source file to build 32-bit DLLfordll.forFortran source file for FPS NT 1.0 referencefordll32.f90Fortran source file for the 32-bit DLLvbstring.vbpVisual Basic 4.0 project filevbstring.frmVisual Basic form including event handlers
Building DLLsThis sample assumes you are using the following development environments:
Microsoft Visual C++, version 4.0Microsoft FORTRAN PowerStation for Windows 95 and Windows NT, version 4.0You will need to setup the environment before running BUILDDLL.BAT.At an MS-DOS command-line prompt type:

C:\MSDEV\BIN\VCVARS32.BAT Build the C DLL. At the command-line prompt type:

BUILDDLL C Build the Fortran DLL. At the same prompt type:

BUILDDLL Fortran Start Visual Basic 4.0 and open the project file VBSTRING.VBP.Run the Visual Basic application by creating an executable file (.exe)or running the program from within Visual Basic.IMPORTANT: The DLLs must be in the \WINDOWS\SYSTEM directory, the directory containing the Visual Basic executable file, or one of the environment file paths.

Notes on Arrays in Different LanguagesGiven an upper bound of n, note that in Visual Basic all of the arrayranges are specified as “1 to n”. When a lower bound is not specified,Visual Basic, by default, assumes 0<=index<=n where “n” is inclusive. C array subscripts are assumed to be in the range 0<=index<=n-1. Fortran array ranges are 1<=index<=n. You need to be careful when you assign the index ranges, and make sure that they match. If you exceed them, you will probably get an Access Violation, an Unhandled Exception, or a random run-time error in your Visual Basic Application.

LanguageDeclarationRange# Items in Array—————————————-BasicDim X(10) As Integer0<=index<=1011Basic*Dim X(1 to 10) As Integer1<=index<=1010Cint X[10];0<=index<=910Fortraninteger X(10)1<=index<=1010Fortran*integer X(0:9)0<=index<=910* With lower bound specified. C is always zero-based.

Symptoms
AdoGUIDz.exe is a self-extracting executable that contains a sample project that demonstrates using the globally unique identifier datatype (GUID) with Microsoft Access, SQL 6.5 and SQL 7.0. The sample code may be particularly helpful if you are attempting to manipulate GUIDs with ODBC versions 3.51 and below because those versions of ODBC do not support a native GUID datatype. ODBC versions 3.6 and above include the GUID datatype. Consequently, the methods for manipulating GUIDs with ODBC 3.6 are simpler.
NOTE: SQL 6.5 does not support a native GUID datatype so in order to store/retrieve GUIDs in SQL 6.5 you must use the VarBinary datatype and Byte Arrays.
Resolution
The following files are available for download from the Microsoft Download Center:
Adoguidz.exe(http://download.microsoft.com/download/vb60pro/sample/1/win98/en-us/adoguidz.exe) Release Date: DEC-29-1998
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.

FileNameSize———————————————————AdoGUID.bas3KBAdoGUID.exe60KBAdoGUID.frm25KBAdoGUID.frx1KBAdoGUID.mdb80KBAdoGUID.vbp2KBReadme.txt4KB Microsoft Access has a ReplicationID AutoNumber field that is a 16-byte (128 bit) Globally Unique Identifier (GUID) that uniquely identifies each record in the database. Please reference the sample project for the code that demonstrates how to SELECT specific GUIDs and Insert GUIDs using the AutoNumber field with Microsoft Access. The following function is a code snippet from the sample that demonstrates how to SELECT a specific GUID from an Access table using Microsoft ActiveX Data Objects (ADO):
Sample Code

Sub AccessReQueryADO()On Error GoTo ErrorMessageDim adoCn As adoDb.ConnectionDim adoRs As adoDb.RecordsetDim strCn As StringDim strSQL As StringstrCn = App.Path & “\adoGUID.mdb”Set adoCn = New adoDb.ConnectionWith adoCn.Provider = “Microsoft.JET.OLEDB.3.51″.CommandTimeout = 500.ConnectionTimeout = 500.Open strCn, “admin”, “”End WithIf Option7.Value = True ThenstrSQL = “SELECT * FROM GUIDtable WHERE ” & _”Instr(1,[colGUID],’” & strGUID & “‘)”ElsestrSQL = “SELECT * FROM GUIDtable”End IfSet adoRs = New adoDb.RecordsetWith adoRsSet .ActiveConnection = adoCn.LockType = adLockOptimistic.CursorLocation = adUseServer.CursorType = adOpenForwardOnlyEnd WithadoRs.Open strSQLtxtMessage.Text = “”While Not adoRs.EOFtxtMessage.Text = txtMessage.Text & _adoRs.Fields(“colGUID”).Value & “|”txtMessage.Text = txtMessage.Text & _adoRs.Fields(“colDescription”).Value & vbCrLfadoRs.MoveNextWendGoTo ExitSubErrorMessage:MsgBox Err.Number & ” : ” & vbCrLf & Err.DescriptionExitSub:Label6.Caption = “- ReQueried AccessADO GUID Table…”Set adoCn = NothingSet adoRs = NothingEnd Sub
Unlike SQL 6.5, SQL 7.0 supports a GUID datatype known as UNIQUEIDENTIFIER. This datatype is a 16-byte GUID stored in the same format as the Microsoft Access AutoNumber (GUID) datatype. There are subtle differences concerning how to Insert and Retrieve the GUIDs among the different database engines. Since SQL 6.5 does not support a native GUID datatype the methods used for storing/retrieving GUIDs are much different than SQL 7.0 or Microsoft Access. SQL 7.0 with the OLEDB provider is almost the same as Microsoft Access with or without the OLEDB provider (SQLOLEDB), as you will see in the sample code. With SQL 6.5 you must store the GUID as a VarBinary(16) datatype. Consequently, to retrieve the GUID with SQL65 you must use a stored procedure and build a Command object with the appropriate ByteArray parameter to pass to the stored procedure SELECT statement.
NOTE: You must use the same code techniques with SQL 7.0 as with SQL 6.5 if you are using the ODBC provider (MSDASQL) since in ODBC 3.51 and below the GUID datatype is not recognized.
The 16-byte (128 bit) data structure of a GUID:

typedef struct _GUID{unsigned longData1;unsigned shortData2;unsigned shortData3;unsigned charData4[8];} GUID;

* Data1An unsigned long integer data value.* Data2An unsigned short integer data value.* Data3An unsigned short integer data value.* Data4An array of unsigned characters. To demonstrate GUIDs with SQL 7.0 or SQL 6.5 in the sample project you must specify a valid (test) SQL 7.0/SQL 6.5 server and database. To do so, navigate to the Connection Info tab and change the Server and Database reference. The defaults are (local) Server and the Pubs database. Also, to use the native GUID datatype for SQL 7.0, you must change to the OLEDB provider (SQLOLEDB) by clicking the appropriate option button in the Provider frame at the top of the Form. If you select ODBC as the provider for SQL 7.0 then the application uses the same code as with SQL 6.5.
NOTE: The Connection Info tab has no bearing on Microsoft Access. The default database for Microsoft Access is included with the sample project AadoGUID.mdb as should reside in the Application path.
For each database you select in the sample project you must run Create Table to create the GUID test table and then Insert to automatically generate some test GUID data before running a ReQuery.
NOTE: CoCreateGUID() is called in the Insert sample code to generate the test GUID values.
In the sample application there is a ByteArray2GUID() function that is used to convert the VarBinary(16) byte array to a GUID string for display. Also, the function is used to convert the GUID string to a byte array for storage in the SQLServer VarBinary(16) datatype column. Note that the function is needed when using the GUIDs interchangeably between Microsoft Access and SQL 6.5. If you Export the Microsoft Access table to SQL 6.5 you will see that the bytes are not stored in the same order in which they display in the Microsoft Access table view. For example:

Reversed…Not Reversed…>—————-<|>—————<20C68F83-9593-0011-BFBB-00C04F8F8347 ‘SQLServer view after table Export.838FC620-9395-1100-BFBB-00C04F8F8347 ‘Microsoft Access view. NOTE: The bytes are in (DWord and Word) reverse order after Exporting the Microsoft Access table.
Because the Microsoft Access Upsizing Wizard results in the same storage of the bytes in SQL 6.5, you must use the ByteArray2GUID() and GUID2ByteArray() functions to remain compatible with the storage of the GUIDs in Microsoft Access. If you do not need to Export the Microsoft Access table to SQL 6.5 or upsize the Microsoft Access database to SQL 6.5 then you need only store the bytes in a straightforward fashion.
The following is a code snippet from the code sample that demonstrates the storage of the GUID in the byte format of Microsoft Access.
Sample Code

Sub SQL65InsertGUID()’Insert GUID record.On Error GoTo ErrorMessageDim adoCn As adoDb.ConnectionDim adoRs As adoDb.RecordsetDim strGUIDtmp As StringDim bytGUID() As ByteDim strCn As StringDim strSQL As StringstrCn = “Provider=” & strProvider & _”;Driver={SQL Server}” & _”;Server=” & txtServer & _”;Database=” & txtDatabase & _”;Uid=” & txtUserID & _”;Pwd=” & txtPasswordSet adoCn = New adoDb.ConnectionWith adoCn.ConnectionString = strCn.CommandTimeout = 500.ConnectionTimeout = 500.OpenEnd WithstrGUIDtmp = strGUIDbytGUID = GUID2ByteArray(FilterGUID(strGUIDtmp))strSQL = “SELECT * FROM GUIDtable WHERE 1=0″Set adoRs = New adoDb.RecordsetWith adoRsSet .ActiveConnection = adoCn.LockType = adLockOptimistic.CursorLocation = adUseServer.CursorType = adOpenForwardOnlyEnd WithadoRs.Open strSQLadoRs.AddNewadoRs.Fields(“colGUID”).Value = bytGUIDadoRs.Fields(“colDescription”).Value = “This is a test GUID”adoRs.UpdateGoTo ExitSubErrorMessage:MsgBox Err.Number & ” : ” & vbCrLf & Err.DescriptionExitSub:Label6.Caption = “[ASCII 176] Inserted SQL65 GUID Record…”Set adoCn = NothingSet adoRs = NothingEnd Sub’======================Function GUID2ByteArray(ByVal strGUID As String) As Byte()Dim i As IntegerDim j As IntegerDim sPos As IntegerDim OffSet As IntegerDim sGUID(0 To 2) As ByteDim bytArray() As ByteReDim bytArray(0 To 15) As BytesGUID(0) = 7sGUID(1) = 11sGUID(2) = 15OffSet = 0sPos = 0′AABBCCDD-AABB-CCDD-XXXX-XXXXXXXXXXXX ‘Microsoft Access view.’DDCCBBAA-BBAA-DDCC-XXXX-XXXXXXXXXXXX ‘SQLServer view.’Need to loop through to build the GUID byte array in the Microsoft’Access storage format since the first eight bytes are reversed.For i = 0 To UBound(sGUID)For j = sGUID(i) To (OffSet + 1) Step -2bytArray(sPos) = “&H” & Mid$(strGUID, j, 2)sPos = sPos + 1Next jOffSet = sGUID(i)Next iFor i = 17 To 31 Step 2bytArray(sPos) = “&H” & Mid$(strGUID, i, 2)sPos = sPos + 1Next iGUID2ByteArray = bytArray()End Function

Symptoms
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
Resolution
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe(http://download.microsoft.com/download/excel2000/sample/12_27_2000/w98nt42kme/en-us/excelado.exe)Release Date: December 12, 2000
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 ServicesMicrosoft 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.Why Use ADO?The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel: Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format ismaintained. If you require “conditional” formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.Jet OLE DB Provider Specifics for Excel WorkbooksThe Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks: Excel 3.0Excel 4.0Excel 5.0Excel 8.0NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:

Dim oConn As New ADODB.ConnectionWith oConn.Provider = “Microsoft.Jet.OLEDB.4.0″.Properties(“Extended Properties”).Value = “Excel 8.0″.Open “C:\Book1.xls”‘…..CloseEnd With ?????-or-

Dim oConn As New ADODB.ConnectionoConn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=C:\Book1.xls;” & _”Extended Properties=”"Excel 8.0;”"”oConn.Close Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.

oRS.Open “Select * from [Sheet1$]“, oConn, adOpenStatic Use a range with a defined name (for example, [Table1]).

oRS.Open “Select * from Table1″, oConn, adOpenStatic Use a range with a specific address (for example, [Sheet1$A1:B10]).

oRS.Open “Select * from [Sheet1$A1:B10]“, oConn, adOpenStatic Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to “guess” the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.Files Included with the SampleThe ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project FilesExcelADO.vbpForm1.frmForm1.frxActive Server PagesEmpData.aspOrders.aspMicrosoft Excel WorkbooksOrdersTemplate.xlsEmpDataTemplate.xlsProductsTemplate.xlsSourceData.xlsMicrosoft Access DatabaseData.mdbHow to Use the SampleExtract the contents of the .exe file to a folder.
To use the Visual Basic project: In Visual Basic, open the ExcelADO.vbp file. On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.Press the F5 key to run the program. A form for the demonstration appears.Click Sample 1.This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:On the Insert menu in Excel, select Names, and then select Define. In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel’s OFFSET function, to compute a total on the data added to the worksheet. Quit Microsoft Excel and return to the Visual Basic application.Click Sample 2.This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3.This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new “Products” worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any “conditional” formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4.This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet (“Sheet1$” or “Sheet2$”) for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.To use the Active Server Pages (ASP):Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.Copy the following files to the folder you created in the previous step:EmpData.aspOrders.aspData.mdbEmpDataTemplate.xlsOrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application. (c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.