nl4jy
asked on
Autoselect dao version when form is open
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("autonu m","ODBC;D SN=SMS",db DriverComp leteRequir ed)) Then
Exit Sub
End If
strSQL = "Select ID from autonum"
set Rs = conDB.openrecordset(strsql ,dbopensna pshot)
'rs.movelast
rs.movefirst
mynum = rs(0)
Set objPage = Item.GetInspector.Modified FormPages( "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.CreateObj ect("DAO.d bEngine.35 ") <~~~~~~~ error over here
Set wrkODBC = dbe.CreateWorkspace("ODBCw orkspace", strUser, strPass, dbUseODBC)
dbe.Workspaces.Append wrkODBC
Set conDB = wrkODBC.OpenConnection("Co nnection1" , MyPrompt, , MyConn)
GetODBCConnection = True
End Function
Function Item_Close()
conDB.Close
wrkODBC.Close
End Function
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("autonu
Exit Sub
End If
strSQL = "Select ID from autonum"
set Rs = conDB.openrecordset(strsql
'rs.movelast
rs.movefirst
mynum = rs(0)
Set objPage = Item.GetInspector.Modified
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.CreateObj
Set wrkODBC = dbe.CreateWorkspace("ODBCw
dbe.Workspaces.Append wrkODBC
Set conDB = wrkODBC.OpenConnection("Co
GetODBCConnection = True
End Function
Function Item_Close()
conDB.Close
wrkODBC.Close
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oops, actually it's the next line over. at
Set wrkODBC = dbe.CreateWorkspace("ODBCw orkspace", strUser, strPass, dbUseODBC)
Set wrkODBC = dbe.CreateWorkspace("ODBCw
ASKER
Oops, actually it's the next line over. at
Set wrkODBC = dbe.CreateWorkspace("ODBCw orkspace", strUser, strPass, dbUseODBC)
Set wrkODBC = dbe.CreateWorkspace("ODBCw
ASKER
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.
I got a reference to it in VS and it is the MS Remote Data Object 2.0.
ASKER
That's fine. As long as this program seems to be working, U deserve the points. Thanks.
Thank you
ASKER
Can't load DLL: 'MSRDO20.DLL'
on the same error line.
Any idea what this mean and how to fix it?