Visual Basic Q&A

Symptoms
With Microsoft Visual Basic versions 4.0, 5.0, and 6.0 for Windows, you canspecify your ODBC (Open Database Connectivity) driver and server in yourconnect string when using RDO (Remote Data Object) and DAO (Data AccessObjects) which eliminates the need to set up a DSN (Data Source Name). Wecall this a “DSN- Less” ODBC connection because you do not need to set up aDSN in order to access your ODBC database server.
To do this, you specify a “driver=” and “server=” parameter in your connectstring as in the following example.
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.

cnstr = “driver={SQL Server};server=myserver;” & _”database=mydb;Username=<username>;PWD=<strong password>;dsn=;”Set cn = en.OpenConnection(“”, False, False, cnstr)
NOTE: The driver name must be surrounded by curly brackets. For example:”{SQL Server}.”
(CAUTION: DSN-Less connections will not work in Visual Basic 4.0 16-bit. Ifyou try to use them you will get a General Protection Fault in moduleODBC.DLL at 0006:080F.)
Resolution
In Microsoft Visual Basic version 3.0 for Windows, you had to create a DSNthat added an extra step when distributing your application because eachworkstation had to have the DSN created in order to access the specifiedserver and database. This was done either manually with the ODBC Adminutility, through code with the RegisterDatabase function, or through codewith the SQLConfigDatasource API function. For additional information onhow to do this setup manually, please see the following articles in theMicrosoft Knowledge Base:
123008?(http://support.microsoft.com/kb/123008/EN-US/)TITLE: How to Set Up ODBC Data Sources When Distributing an App
126940?(http://support.microsoft.com/kb/126940/EN-US/): RegisterDatabase Fails After ODBC Version 2.x Installed
132329?(http://support.microsoft.com/kb/132329/EN-US/): RegisterDatabase Method Does Not Modify ODBC.INI File
Sample ProgramThe following RDO example uses a “DSN-less” ODBC connection so you do notneed to set up a DSN with the ODBC Admin utility beforehand.
Start a new project in Visual Basic. Form1 is created by default.Add a command button to Form1, Command1 by default.Paste the following code into 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.

Dim en As rdoEnvironmentDim cn As rdoConnectionPrivate Sub Form_Load()MousePointer = vbHourglassDim strConnect As String’ Change the next line to reflect your driver and server.strConnect = “driver={SQL Server};server=jonfo5;” & _”database=pubs;Username=<username>;PWD=<strong password>;”Set en = rdoEngine.rdoEnvironments(0)Set cn = en.OpenConnection( _dsName:=”", _Prompt:=rdDriverNoPrompt, _ReadOnly:=False, _Connect:=strConnect)cn.QueryTimeout = 600MousePointer = vbNormalEnd SubPrivate Sub Command1_Click()MousePointer = vbHourglassDim rs As rdoResultsetSet rs = cn.OpenResultset(Name:=”Select * from authors”, _Type:=rdOpenForwardOnly, _LockType:=rdConcurReadOnly, _Options:=rdExecDirect)Debug.Print rs(0), rs(1), rs(2)MousePointer = vbNormalEnd Sub Note that you must change your DRIVER, SERVER, DATABASE, UID, and PWDparameters in the OpenConnection method. You also need to modify the SQLstatement contained in the Command1_Click event to match your own SQLdata source.Check the Microsoft Remote Data Object in the Project References.Start the program or press the F5 key.Click the Command1 button to create a rdoResultset and display the firstrow of data in the debug window.

Symptoms
The Data Access Object (DAO) documentation that comes with Microsoft Access and Visual Basic Online help shows how to open a Paradox table. However, it does not show how to open a table that is password protected.
Resolution
To open a Paradox table, the documentation states that you must specify Paradox 3.x, Paradox 4.x, or Paradox 5.x in the connect string. However, the documentation omits the PWD argument in the connect string. PWD is used to specify the Paradox password, just as it is used when opening password-protected ODBC or Jet tables.
The following code samples show the correct use of the PWD option when opening password-protected Paradox tables. In each of the samples, the table is C:\MYDATA\Table1.DB and the password is “ABC”.
NOTE: The period in the Paradox file name needs to be replaced by the # symbol. This is because Microsoft Jet uses the period to separate database and table names and to separate table and field names. For example, Table1.DB needs to be written as Table1#DB when referring to the table in Jet.

Attaching/Linking a password-protected table:

Dim db As DAO.DatabaseDim td As DAO.TableDefDim rs As DAO.RecordsetSet db = OpenDatabase(App.Path & “\NWIND.MDB”)Set td = db.CreateTableDef(“Table1″)td.Connect = “Paradox 4.x;PWD=ABC;DATABASE=C:\MYDATA;”td.SourceTableName = “Table1#DB”db.TableDefs.Append tdSet rs = db.OpenRecordset(“Table1″)

Opening directly using a Database object:In this case, the database is the directory where the tables reside. If all tables have the same password, you can open them from the same Database object. If the tables have different passwords, you will need to open a different Database object for each unique password.

Set db = DBEngine(0).OpenDatabase(“C:\MYDATA”, 0, 0, “Paradox 4.x;PWD=ABC;”)Set rs = db.OpenRecordset(“Table1#DB”)

Referencing a Paradox table directly in SQL statementsMicrosoft Jet allows use of an IN clause or the [Database].[Table] syntax to reference a table outside the current database. The [Database].[Table] syntax is preferred.

Set db = OpenDatabase(App.Path & “\NWIND.MDB”)Set rs = db.OpenRecordset(“SELECT * FROM [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB]“)” Or’Set rs = db.OpenRecordset(“SELECT * FROM [Table1#DB] IN ” [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC]“)” Or’Set rs = db.OpenRecordset(“SELECT * FROM [Table1#DB] IN ‘C:\MYDATA’ [Paradox 3.x;PWD=ABC]“)
You can also use action queries with the same direct SQL technique:

‘ Make a new table Access “LocalTable” from data in Paradox table.Set db = OpenDatabase(App.Path & “\NWIND.MDB”)db.Execute “SELECT * INTO LocalTable FROM [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB]“‘ Append data into an Access table “LocalTable” from Paradox table.db.Execute “INSERT INTO LocalTable SELECT * FROM [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB]“‘ Append data into a Paradox table from an Access table “LocalTable”. db.Execute “INSERT INTO [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB] SELECT * FROM LocalTable”‘ Delete records from a Paradox table.db.Execute “DELETE FROM [Paradox 3.x;DATABASE=C:\MYDATA;PWD=ABC].[Table1#DB] WHERE Field1 = 55″
(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Malcolm Stewart, Microsoft Corporation.

Symptoms
This article describes how to attach and create Querydefs on external ODBCtables. The method for opening external ODBC tables is to attach the tablesto an .mdb file.
Jet does not support named QueryDefs on a non-attached ODBC database. A non-attached ODBC database is one that is opened directly with the OpenDatabasemethod of the WorkSpace object without the use of an .mdb file.
If it is not appropriate for the application to attach the ODBC tables, itis possible to create Querydefs with no name to accomplish the procedure.
For additional information, please see the following article in theMicrosoft Knowledge Base:
149055?(http://support.microsoft.com/kb/149055/EN-US/): Jet Doesn’t Support QueryDefs on a Non-Attached ODBC Table
Resolution
The following is information from “Guide To Data Access Objects,” Chapter7, Data Access Choices, that explains this procedure:
In many cases, attaching a table to access external data is faster thanopening a table directly, especially if the data is located in an ODBCdatabase. In Visual Basic version 4.0, SQL Passthrough is used to queryattached ODBC databases directly. Consider attaching external tables ratherthan opening them directly. Using external data in an ODBC databaserequires opening the external tables directly so performance issignificantly slower when using the data.
Sample ProgramThe following example describes how to attach to and create a Querydef onan ODBC table using a “DSN-less” ODBC connection. With this procedure, itis not necessary to set up a DSN with the ODBC Admin utility.
Start a new project in Visual Basic. Form1 is created by default.Add three Command buttons to Form1: Command1, Command2, Command3 bydefault.Paste the following code in the General Declarations section of Form1:

Dim db As DatabaseDim cn As StringPrivate Sub Form_Load()cn = “odbc;driver={SQL Server};server=myserver;” & _”database=pubs;uid=myuid;pwd=mypwd”If Dir(“mydb.mdb”) <> “” Then’ database exists, so just open it.Set db = OpenDatabase(Name:=”mydb”, Exclusive:=False, _ReadOnly:=False, Connect:=”")Else’database does not exist, create it and attach authors table.Set db = CreateDatabase(Name:=”mydb”, Connect:=dbLangGeneral, _Option:=dbVersion30)Dim td As TableDefSet td = db.CreateTableDef()td.Name = “Authors”td.SourceTableName = “Authors”td.Connect = cnEnd IfEnd SubPrivate Sub Command1_Click()Dim qd As QueryDefOn Error Resume NextSet qd = db.QueryDefs(“abc”)’ test for existence of querydef.If Error > 0 ThenSet qd = db.CreateQueryDef(Name:=”abc”)qd.Connect = cnqd.SQL = “Select @@Version” ‘native SQL ServerEnd IfSet qd = db.QueryDefs(“xyz”)’ test for existence of querydef.If Error > 0 ThenSet qd = db.CreateQueryDef(Name:=”xyz”)qd.Connect = cnqd.SQL = “Select * from titles” ‘ generic SQL.End IfOn Error GoTo 0End SubPrivate Sub Command2_Click()Dim rs As RecordsetDim qd As QueryDefSet qd = db.QueryDefs(“abc”)Set rs = qd.OpenRecordset()Call displayResults(rs)End SubPrivate Sub Command3_Click()Dim rs As RecordsetDim qd As QueryDefSet qd = db.QueryDefs(“xyz”)Set rs = qd.OpenRecordset()Call displayResults(rs)End SubSub displayResults(rs As Recordset)Dim f As Field, s As String, i As IntegerFor Each f In rs.Fieldss = s & f.NameNext fDebug.Print s’ print column headers.While Not rs.EOF And i < 5s = “”For Each f In rs.Fieldss = s & f.ValueNext fDebug.Print s’ print first 5 rows.rs.MoveNexti = i + 1WendEnd Sub NOTE: You need to change the DRIVER, SERVER, DATABASE, UID, and PWDparameters in the OpenConnection method. Also you must modify the SQLstatements contained in the Command1_Click event to match your SQL datasource.
Press the F5 key to start the program.Click the Command1 button to create the Querydefs. Click the Command2and Command3 buttons to execute the Querydefs. Note that the first fiverows of data appear in the Debug window.

Symptoms
This article describes how to call system-stored procedures on SQL Serverfrom RDO.
SQL Server has a number of prewritten, stored procedures that are used toconfigure and administer the SQL server. They are all located in the Masterdatabase and have the prefix [ASCII 147]sp_,[ASCII 148] which distinguishes them fromuser- or application-written stored procedures.
In order to call these stored procedures from RDO, you must specify thecorrect database in which they reside, the master database. There areseveral ways to do this, but the most effective way is to explicitlyreference the stored procedure in your call syntax. For example:

{ ? = call master.dbo.sp_addlogin(?,?) }
Another method to accomplish this behavior is to set the default databasebefore creating and executing your stored procedure. The following exampleuses a prepared statement and assumes that “Pubs” is your default database,cn is an active rdoConnection object, and qr is an rdoQuery object:

cn .Execute “Use Master”qr.SQL= “some sql to execute…”Set qr.ActiveConnection = cnqr .Executecn .Execute “Use Pubs”
Resolution
Start a new Standard EXE project. Form1 is created by default.Add a CommandButton (Command1) to Form1.From the Project menu, select References, and place a check next toMicrosoft Remote Data Object 2.0.Paste the following code into the General Declarations section of Form1:

Private Sub Command1_Click()Dim cn As New rdoConnectionDim qr As New rdoqueryWith cn.Connect = “Driver={SQL Server};Server=MyServer;” & _”Database=pubs;Uid=<username>;Pwd=<strong password>”.EstablishConnection Prompt = rdDriverNoPromptEnd WithWith qrSet .ActiveConnection = cn’Note: If you don’t specify master, you will get the following’error: “An invalid parameter was passed.”.SQL = “{ ? = call master.dbo.sp_addlogin(?,?) }”.Prepared = True.BindThreshold = 1024 ‘largest column that will be bound under’ODBC.Debug.Print .rdoParameters.Count.rdoParameters(0).Direction = rdParamOutput.rdoParameters(1).Direction = rdParamInput.rdoParameters(2).Direction = rdParamInput.rdoParameters(1) = “Edward”.rdoParameters(2) = “Scissorhands”.ExecuteDebug.Print .rdoParameters(0).Value’Const SQL_SUCCESS As Long = 0′Const SQL_ERROR As Long = -1End WithEnd Sub Note that you need to change your DATABASE, UID, and PWD parameters inthe Connect Property.Start the program or press the F5 key.Click the Command1 button to execute the stored procedure and displaythe parameter count and the output parameter in the debug window.

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
When you attempt to connect to a Microsoft Access 2000 database by using either the standard Data control or the Data Form Wizard, you see the following error message where ‘…\databasename.mdb’ is the absolute filename of the Access 2000 database:

Unrecognized database format ‘…\databasename.mdb’”
Resolution
The standard Data control uses Data Access Object (DAO) 3.51 to connect to Access databases by using the Jet 3.51 engine.
The Data Form Wizard uses the Jet 3.51 OLE DB Provider to connect to Access databases by using the Jet 3.51 engine.
The Jet 3.51 engine can connect to Access database versions prior to Access 2000, but cannot connect to Access 2000 databases.