Visual Basic Q&A

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 get more information on the ODBC Call Failederror. When errors occur using ODBC databases, Visual Basic will provide an”ODBC Call Failed” error message. This generic error message provides nospecific detail so you must cycle through the Errors collection to getadditional information. Below is a code sample that shows the difference inbehavior.
Resolution
The DBEngine has an Errors collection that can be manipulated by the FOR-EACH construct. The JET Engine can store multiple errors in the DBEngineErrors collection. In Visual Basic 3.0, it was possible to parse the stringusing the routine shown on Page 175 of the Visual Basic 4.0 ProfessionalFeatures Book under the “Guide to Data Access Objects” section. The #symbol was used to separate the “ODBC Call Failed” message from thedetailed ODBC description in Visual Basic 3.0. However, this is notnecessary under Visual Basic versions 4.0 and 5.0.
For the example below, a two-field table called MyTable has been set up onan ODBC Source and a primary key set on the ID Field. Two records have beenadded as below:

FieldIDDescription===============================Record 11HelloRecord 22World
The code below will generate an error by trying to add a record with aduplicate primary key value to test the code:
Start a new Standard EXE project. Form1 is added by default.Add a CommandButton to Form1.Add the following code to the General Declarations section of Form1:

Option ExplicitPrivate Sub Command1_Click()Dim db As DatabaseDim rs As RecordsetOn Error GoTo trapSet db = OpenDatabase(“”)Set rs = db.OpenRecordset(“Select * from MyTable”)rs.AddNewrs.Fields(0).Value = 2rs.UpdateExit Subtrap:MsgBox Errors.CountMsgBox Err.Number & ” ” & Err.DescriptionEnd Sub Press the F5 key to run the project. Click on the CommandButton and youshould receive error 3146, “ODBC Call Failed.” Although the Error countis greater than one, only one message will be displayed.Remove the code from within the error trap and replace it with oneof the following error handlers:

‘ DAO Error HandlerDim MyError As ErrorMsgBox Errors.CountFor Each MyError In DBEngine.ErrorsWith MyErrorMsgBox .Number & ” ” & .DescriptionEnd WithNext MyError’ RDO Error HandlerDim MyError As rdoErrorMsgBox rdoErrors.CountFor Each MyError In rdoEngine.rdoErrorsWith MyErrorMsgBox .Number & ” ” & .DescriptionEnd WithNext MyError Press the F5 key to run the project. You should see a detailed messageand then the 3146 Error for “ODBC Call Failed.”

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
This article illustrates how to programmatically retrieve IP configuration information similar to the IPCONFIG.EXE utility. It demonstrates how to use the IP Helper APIs GetNetworkParams() and GetAdaptersInfo() from Visual Basic.
The libraries called by the code sample in this article are only supportedon the following platforms:
Microsoft Windows 2000
Microsoft Windows 98
Microsoft Windows Millennium Edition (Me)Running it on any other platform results in an error.
Resolution
Start a new Visual Basic Standard EXE project. Form1 is created by default.On the Project menu, click Remove Form1.On the Project menu, click Add Module. Module1 is created by default.Paste the following code in the General Declarations section of Module1:

Public Const MAX_HOSTNAME_LEN = 132Public Const MAX_DOMAIN_NAME_LEN = 132Public Const MAX_SCOPE_ID_LEN = 260Public Const MAX_ADAPTER_NAME_LENGTH = 260Public Const MAX_ADAPTER_ADDRESS_LENGTH = 8Public Const MAX_ADAPTER_DESCRIPTION_LENGTH = 132Public Const ERROR_BUFFER_OVERFLOW = 111Public Const MIB_IF_TYPE_ETHERNET = 6Public Const MIB_IF_TYPE_TOKENRING = 9Public Const MIB_IF_TYPE_FDDI = 15Public Const MIB_IF_TYPE_PPP = 23Public Const MIB_IF_TYPE_LOOPBACK = 24Public Const MIB_IF_TYPE_SLIP = 28Type IP_ADDR_STRINGNext As LongIpAddress As String * 16IpMask As String * 16Context As LongEnd TypeType IP_ADAPTER_INFONext As LongComboIndex As LongAdapterName As String * MAX_ADAPTER_NAME_LENGTHDescription As String * MAX_ADAPTER_DESCRIPTION_LENGTHAddressLength As LongAddress(MAX_ADAPTER_ADDRESS_LENGTH – 1) As ByteIndex As LongType As LongDhcpEnabled As LongCurrentIpAddress As LongIpAddressList As IP_ADDR_STRINGGatewayList As IP_ADDR_STRINGDhcpServer As IP_ADDR_STRINGHaveWins As BytePrimaryWinsServer As IP_ADDR_STRINGSecondaryWinsServer As IP_ADDR_STRINGLeaseObtained As LongLeaseExpires As LongEnd TypeType FIXED_INFOHostName As String * MAX_HOSTNAME_LENDomainName As String * MAX_DOMAIN_NAME_LENCurrentDnsServer As LongDnsServerList As IP_ADDR_STRINGNodeType As LongScopeIdAs String * MAX_SCOPE_ID_LENEnableRouting As LongEnableProxy As LongEnableDns As LongEnd TypePublic Declare Function GetNetworkParams Lib “IPHlpApi.dll” _(FixedInfo As Any, pOutBufLen As Long) As LongPublic Declare Function GetAdaptersInfo Lib “IPHlpApi.dll” _(IpAdapterInfo As Any, pOutBufLen As Long) As LongPublic Declare Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” _(Destination As Any, Source As Any, ByVal Length As Long)Sub main()Dim error As LongDim FixedInfoSize As LongDim AdapterInfoSize As LongDim i As IntegerDim PhysicalAddressAs StringDim NewTime As DateDim AdapterInfo As IP_ADAPTER_INFODim AddrStr As IP_ADDR_STRINGDim FixedInfo As FIXED_INFODim Buffer As IP_ADDR_STRINGDim pAddrStr As LongDim pAdapt As LongDim Buffer2 As IP_ADAPTER_INFODim FixedInfoBuffer() As ByteDim AdapterInfoBuffer() As Byte’ Get the main IP configuration information for this machine’ using a FIXED_INFO structure.FixedInfoSize = 0error = GetNetworkParams(ByVal 0&, FixedInfoSize)If error <> 0 ThenIf error <> ERROR_BUFFER_OVERFLOW ThenMsgBox “GetNetworkParams sizing failed with error ” & errorExit SubEnd IfEnd IfReDim FixedInfoBuffer(FixedInfoSize – 1)error = GetNetworkParams(FixedInfoBuffer(0), FixedInfoSize)If error = 0 ThenCopyMemory FixedInfo, FixedInfoBuffer(0), FixedInfoSizeMsgBox “Host Name:” & FixedInfo.HostNameMsgBox “DNS Servers:” & FixedInfo.DnsServerList.IpAddresspAddrStr = FixedInfo.DnsServerList.NextDo While pAddrStr <> 0CopyMemory Buffer, ByVal pAddrStr, LenB(Buffer)MsgBox “DNS Servers:” & Buffer.IpAddresspAddrStr = Buffer.NextLoopSelect Case FixedInfo.NodeTypeCase 1MsgBox “Node type: Broadcast”Case 2MsgBox “Node type: Peer to peer”Case 4MsgBox “Node type: Mixed”Case 8MsgBox “Node type: Hybrid”Case ElseMsgBox “Unknown node type”End SelectMsgBox “NetBIOS Scope ID:” & FixedInfo.ScopeIdIf FixedInfo.EnableRouting ThenMsgBox “IP Routing Enabled “ElseMsgBox “IP Routing not enabled”End IfIf FixedInfo.EnableProxy ThenMsgBox “WINS Proxy Enabled “ElseMsgBox “WINS Proxy not Enabled “End IfIf FixedInfo.EnableDns ThenMsgBox “NetBIOS Resolution Uses DNS “ElseMsgBox “NetBIOS Resolution Does not use DNS”End IfElseMsgBox “GetNetworkParams failed with error ” & errorExit SubEnd If’ Enumerate all of the adapter specific information using the’ IP_ADAPTER_INFO structure.’ Note:IP_ADAPTER_INFO contains a linked list of adapter entries.AdapterInfoSize = 0error = GetAdaptersInfo(ByVal 0&, AdapterInfoSize)If error <> 0 ThenIf error <> ERROR_BUFFER_OVERFLOW ThenMsgBox “GetAdaptersInfo sizing failed with error ” & errorExit SubEnd IfEnd IfReDim AdapterInfoBuffer(AdapterInfoSize – 1)’ Get actual adapter informationerror = GetAdaptersInfo(AdapterInfoBuffer(0), AdapterInfoSize)If error <> 0 ThenMsgBox “GetAdaptersInfo failed with error ” & errorExit SubEnd If’ Allocate memoryCopyMemory AdapterInfo, AdapterInfoBuffer(0), AdapterInfoSizepAdapt = AdapterInfo.NextDoCopyMemory Buffer2, AdapterInfo, AdapterInfoSizeSelect Case Buffer2.TypeCase MIB_IF_TYPE_ETHERNETMsgBox “Adapter name: Ethernet adapter “Case MIB_IF_TYPE_TOKENRINGMsgBox “Adapter name: Token Ring adapter “Case MIB_IF_TYPE_FDDIMsgBox “Adapter name: FDDI adapter “Case MIB_IF_TYPE_PPPMsgBox “Adapter name: PPP adapter”Case MIB_IF_TYPE_LOOPBACKMsgBox “Adapter name: Loopback adapter “Case MIB_IF_TYPE_SLIPMsgBox “Adapter name: Slip adapter “Case ElseMsgBox “Adapter name: Other adapter “End SelectMsgBox “AdapterDescription: ” & Buffer2.DescriptionPhysicalAddress = “”For i = 0 To Buffer2.AddressLength – 1PhysicalAddress = PhysicalAddress & Hex(Buffer2.Address(i))If i < Buffer2.AddressLength – 1 ThenPhysicalAddress = PhysicalAddress & “-”End IfNextMsgBox “Physical Address: ” & PhysicalAddressIf Buffer2.DhcpEnabled ThenMsgBox “DHCP Enabled “ElseMsgBox “DHCP disabled”End IfMsgBox “IP Address: ” & Buffer2.IpAddressList.IpAddressMsgBox “Subnet Mask: ” & Buffer2.IpAddressList.IpMaskpAddrStr = Buffer2.IpAddressList.NextDo While pAddrStr <> 0CopyMemory Buffer, Buffer2.IpAddressList, LenB(Buffer)MsgBox “IP Address: ” & Buffer.IpAddressMsgBox “Subnet Mask: ” & Buffer.IpMaskpAddrStr = Buffer.NextIf pAddrStr <> 0 ThenCopyMemory Buffer2.IpAddressList, ByVal pAddrStr, _LenB(Buffer2.IpAddressList)End IfLoopMsgBox “Default Gateway: ” & Buffer2.GatewayList.IpAddresspAddrStr = Buffer2.GatewayList.NextDo While pAddrStr <> 0CopyMemory Buffer, Buffer2.GatewayList, LenB(Buffer)MsgBox “IP Address: ” & Buffer.IpAddresspAddrStr = Buffer.NextIf pAddrStr <> 0 ThenCopyMemory Buffer2.GatewayList, ByVal pAddrStr, _LenB(Buffer2.GatewayList)End IfLoopMsgBox “DHCP Server: ” & Buffer2.DhcpServer.IpAddressMsgBox “Primary WINS Server: ” & _Buffer2.PrimaryWinsServer.IpAddressMsgBox “Secondary WINS Server: ” & _Buffer2.SecondaryWinsServer.IpAddress’ Display time.NewTime = DateAdd(“s”, Buffer2.LeaseObtained, #1/1/1970#)MsgBox “Lease Obtained: ” & _CStr(Format(NewTime, “dddd, mmm d hh:mm:ss yyyy”))NewTime = DateAdd(“s”, Buffer2.LeaseExpires, #1/1/1970#)MsgBox “Lease Expires :” & _CStr(Format(NewTime, “dddd, mmm d hh:mm:ss yyyy”))pAdapt = Buffer2.NextIf pAdapt <> 0 ThenCopyMemory AdapterInfo, ByVal pAdapt, AdapterInfoSizeEnd IfLoop Until pAdapt = 0End Sub Press the F5 key to run the project, click OK on each of the message boxes that are displayed, and note the results.Running this sample as compiled, EXE gives the following error message at the end:

Runtime error 10: this array is fixed and temporary locked.Running this inside IDE generates IPF at VB6.EXE at the end.