Solved

Autoselect dao version when form is open

Posted on 2002-05-29
8
291 Views
Last Modified: 2012-06-22
His is actually a continuation to my PAQ "connecting outlook to sql server"

Here's the problem, I have the following code working, but when a workstation have the new DAO that is not compatible with the old one, I would like to have the form autoselect the connection for me.  The coding goes as follows (with arrows pointing at the error)


Option Explicit
'Script level declarations
Dim dbe
Dim wrkODBC
Dim conDB
Dim Rs
Dim gstrAppName
'Dim page objects and control collections
Dim objPage
Dim objControls
Dim objPagePO
Dim objControlsPO
'DAO constants dim
Dim dbUseODBC
Dim dbDriverComplete
Dim dbDriverNoPrompt
Dim dbDriverPrompt
Dim dbDriverCompleteRequired
Dim dbOpenSnapshot
Dim dbOpenForwardOnly
Dim dbOpenDynamic
dbUseODBC=1
dbDriverComplete=0
dbDriverNoPrompt=1
dbDriverPrompt=2
dbDriverCompleteRequired=3
dbOpenSnapshot=4
dbOpenForwardOnly=8
dbOpenDynamic=16
dim MyNum

Dim txtOrderID

Sub Item_open()
Dim curExtension
Dim lngOrderID
Dim i
Dim strSQL
If Not (GetODBCConnection("autonum","ODBC;DSN=SMS",dbDriverCompleteRequired)) Then
    Exit Sub
End If
strSQL = "Select ID from autonum"
set Rs = conDB.openrecordset(strsql,dbopensnapshot)
'rs.movelast
rs.movefirst
mynum = rs(0)
Set objPage = Item.GetInspector.ModifiedFormPages("General")
Set objControls = objPage.Controls                                  
Set txtOrderID = objControls("txtOrderID")              
if txtorderid.text = "0" then
  strSQL = "update autonum set id = id + 1"
  conDB.execute strSQL
  txtOrderID.text = mynum
end if
End Sub

Function GetODBCConnection (ByVal MyDSN, ByVal MyConn, ByVal MyPrompt)
Dim strUser
Dim strPass
Dim dbUseODBC
'Must assign values to constants in VBScript
dbUseODBC=1
strUser=""
strPass=""
Set dbe= Item.Application.CreateObject("DAO.dbEngine.35") <~~~~~~~ error over here
Set wrkODBC = dbe.CreateWorkspace("ODBCworkspace", strUser, strPass, dbUseODBC)
dbe.Workspaces.Append wrkODBC
Set conDB = wrkODBC.OpenConnection("Connection1", MyPrompt, , MyConn)
GetODBCConnection = True
End Function

Function Item_Close()
conDB.Close
wrkODBC.Close
End Function
0
Comment
Question by:nl4jy
  • 5
  • 3
8 Comments
 
LVL 8

Accepted Solution

by:
jessnjeff earned 100 total points
ID: 7043317
You are stuck because of the funky (non)error handling available in a form.

Try adding ON error resume next in your function, then add

code here for your error

Set dbe= Item.Application.CreateObject("DAO.dbEngine.35") <~~~~~~~ error over here

if err.number <> 0 then Set dbe= Item.Application.CreateObject("DAO.dbEngine.36")
0
 
LVL 1

Author Comment

by:nl4jy
ID: 7044856
Okay, I tried that, but now, when I tried to open up the form, I have the following error on the new boxes:

Can't load DLL: 'MSRDO20.DLL'
on the same error line.

Any idea what this mean and how to fix it?
0
 
LVL 1

Author Comment

by:nl4jy
ID: 7044869
Oops, actually it's the next line over.  at

Set wrkODBC = dbe.CreateWorkspace("ODBCworkspace", strUser, strPass, dbUseODBC)
0
 
LVL 1

Author Comment

by:nl4jy
ID: 7044962
Oops, actually it's the next line over.  at

Set wrkODBC = dbe.CreateWorkspace("ODBCworkspace", strUser, strPass, dbUseODBC)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:nl4jy
ID: 7044994
Never mind, that fixed it, I didn't publish the form so the new code didn't save.  Thanks.  but before I hand these points over, if you know what the msrdo20 error is, please do tell.  Thanks again.
0
 
LVL 8

Expert Comment

by:jessnjeff
ID: 7045079
I got a reference to it in VS and it is the MS Remote Data Object 2.0.
0
 
LVL 1

Author Comment

by:nl4jy
ID: 7045234
That's fine.  As long as this program seems to be working, U deserve the points.  Thanks.
0
 
LVL 8

Expert Comment

by:jessnjeff
ID: 7045263
Thank you
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now