User-Defined type not defined?

[Bob V]

I recently imported all my data into a blank DB now I am getting this error
on:
Dim wrk As Workspace
Is that something to do with me importing into a new DB
Thanks for any help...........Bob

Sub SelAllNone(Optional SelectAll As Boolean = True)
On Error GoTo stoprun

Dim sqlStr As String
Dim wrk As Workspace
Dim db As Database

Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb


sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";"
wrk.BeginTrans
db.Execute sqlStr, dbFailOnError
wrk.CommitTrans

Exit_Here:
Set wrk = Nothing
Set db = Nothing
Exit Sub

stoprun:
MsgBox Err.Number & " - " & Err.Description
wrk.Rollback
Resume Exit_Here
End Sub

 

[Douglas J. Steele]

I'm guessing (since you didn't say) that you're using Access 2000 or Access
2002.

Workspace is an object in the DAO object model. By default, neither Access
2000 nor Access 2002 have a reference set for DAO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[Bob V]

Thanks Douglas ticked Microsoft DAO and alls well
Un ticked, Microsoft ActiveX Data Objects 2.1 but had to re select it as it
was in use
Thanks for your time in solve my problem......Bob
Option Explicit
Option Compare Database
Dim recInvoice As ADODB.Recordset, dblGSTContentsValue As Double,
lngIntermediateID As Long
Dim dblGSTOptionsValue As Double
Dim bModify As Boolean, ynCheque As Boolean, lngInvoiceID As Long,
strExpressionOrgument As String
Dim sngGstPercentage As Single, recGSTOptions As ADODB.Recordset
Dim bLockFlag As Boolean

I'm guessing (since you didn't say) that you're using Access 2000 or
Access 2002.

Workspace is an object in the DAO object model. By default, neither Access
2000 nor Access 2002 have a reference set for DAO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO
recordset, you'll need to use Dim rsCurr as DAO.Recordset (to guarantee an
ADO recordset, you'd use Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I recently imported all my data into a blank DB now I am getting this
error on:
Dim wrk As Workspace
Is that something to do with me importing into a new DB
Thanks for any help...........Bob

Sub SelAllNone(Optional SelectAll As Boolean = True)
On Error GoTo stoprun

Dim sqlStr As String
Dim wrk As Workspace
Dim db As Database

Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb


sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";"
wrk.BeginTrans
db.Execute sqlStr, dbFailOnError
wrk.CommitTrans

Exit_Here:
Set wrk = Nothing
Set db = Nothing
Exit Sub

stoprun:
MsgBox Err.Number & " - " & Err.Description
wrk.Rollback
Resume Exit_Here
End Sub

 

[Bob V]

Douglas I noticed in a earlier version of my DB I have
Microsoft ADO ext. 2.8 for DDL and Security
Should I check that as well.............Regards Bob

Thanks Douglas ticked Microsoft DAO and alls well
Un ticked, Microsoft ActiveX Data Objects 2.1 but had to re select it as
it was in use
Thanks for your time in solve my problem......Bob
Option Explicit
Option Compare Database
Dim recInvoice As ADODB.Recordset, dblGSTContentsValue As Double,
lngIntermediateID As Long
Dim dblGSTOptionsValue As Double
Dim bModify As Boolean, ynCheque As Boolean, lngInvoiceID As Long,
strExpressionOrgument As String
Dim sngGstPercentage As Single, recGSTOptions As ADODB.Recordset
Dim bLockFlag As Boolean

I'm guessing (since you didn't say) that you're using Access 2000 or
Access 2002.

Workspace is an object in the DAO object model. By default, neither
Access 2000 nor Access 2002 have a reference set for DAO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one
for Microsoft DAO 3.6 Object Library, and select it. If you're not going
to be using ADO, uncheck the reference to Microsoft ActiveX Data Objects
2.1 Library

If you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO
recordset, you'll need to use Dim rsCurr as DAO.Recordset (to guarantee
an ADO recordset, you'd use Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I recently imported all my data into a blank DB now I am getting this
error on:
Dim wrk As Workspace
Is that something to do with me importing into a new DB
Thanks for any help...........Bob

Sub SelAllNone(Optional SelectAll As Boolean = True)
On Error GoTo stoprun

Dim sqlStr As String
Dim wrk As Workspace
Dim db As Database

Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb


sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";"
wrk.BeginTrans
db.Execute sqlStr, dbFailOnError
wrk.CommitTrans

Exit_Here:
Set wrk = Nothing
Set db = Nothing
Exit Sub

stoprun:
MsgBox Err.Number & " - " & Err.Description
wrk.Rollback
Resume Exit_Here
End Sub

 

[Tom Wickerath]

Hi Bob,

If your code compiles okay without this additional reference, then you do
not need it. It is best to "starve" the references that are checked, since
each one is loaded into memory when you open your database. Use Debug >
Compile {ProjectName} from within any code module, where {ProjectName} is the
name of your VBA project, to compile your code. If nothing appears to happen,
except that this option becomes unavailable immediately afterwards, before
you make any other code changes, then this is a good sign.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[david]

Note, since both DAO and ADO have RecordSet object
you have to be careful that they don't get confused when
you have both ticked. If nothing else, you have to make sure
that they are in the same order in both the old database and
the new database.

(david)

Thanks Douglas ticked Microsoft DAO and alls well
Un ticked, Microsoft ActiveX Data Objects 2.1 but had to re select it as
it was in use
Thanks for your time in solve my problem......Bob
Option Explicit
Option Compare Database
Dim recInvoice As ADODB.Recordset, dblGSTContentsValue As Double,
lngIntermediateID As Long
Dim dblGSTOptionsValue As Double
Dim bModify As Boolean, ynCheque As Boolean, lngInvoiceID As Long,
strExpressionOrgument As String
Dim sngGstPercentage As Single, recGSTOptions As ADODB.Recordset
Dim bLockFlag As Boolean

I'm guessing (since you didn't say) that you're using Access 2000 or
Access 2002.

Workspace is an object in the DAO object model. By default, neither
Access 2000 nor Access 2002 have a reference set for DAO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one
for Microsoft DAO 3.6 Object Library, and select it. If you're not going
to be using ADO, uncheck the reference to Microsoft ActiveX Data Objects
2.1 Library

If you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO
recordset, you'll need to use Dim rsCurr as DAO.Recordset (to guarantee
an ADO recordset, you'd use Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I recently imported all my data into a blank DB now I am getting this
error on:
Dim wrk As Workspace
Is that something to do with me importing into a new DB
Thanks for any help...........Bob

Sub SelAllNone(Optional SelectAll As Boolean = True)
On Error GoTo stoprun

Dim sqlStr As String
Dim wrk As Workspace
Dim db As Database

Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb


sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";"
wrk.BeginTrans
db.Execute sqlStr, dbFailOnError
wrk.CommitTrans

Exit_Here:
Set wrk = Nothing
Set db = Nothing
Exit Sub

stoprun:
MsgBox Err.Number & " - " & Err.Description
wrk.Rollback
Resume Exit_Here
End Sub

 

[Bob V]

Thanks Guys,
My older DB has a reference ticked to Microsoft ADO 2.8 for DDL Security, In
my newer version I don't have that reference only 2.7
Without it ticked I am getting an all clear Debug/Compile, should I just not
tick the 2.7
Thanks for your help....Bob

Note, since both DAO and ADO have RecordSet object
you have to be careful that they don't get confused when
you have both ticked. If nothing else, you have to make sure
that they are in the same order in both the old database and
the new database.

(david)

Thanks Douglas ticked Microsoft DAO and alls well
Un ticked, Microsoft ActiveX Data Objects 2.1 but had to re select it as
it was in use
Thanks for your time in solve my problem......Bob
Option Explicit
Option Compare Database
Dim recInvoice As ADODB.Recordset, dblGSTContentsValue As Double,
lngIntermediateID As Long
Dim dblGSTOptionsValue As Double
Dim bModify As Boolean, ynCheque As Boolean, lngInvoiceID As Long,
strExpressionOrgument As String
Dim sngGstPercentage As Single, recGSTOptions As ADODB.Recordset
Dim bLockFlag As Boolean

I'm guessing (since you didn't say) that you're using Access 2000 or
Access 2002.

Workspace is an object in the DAO object model. By default, neither
Access 2000 nor Access 2002 have a reference set for DAO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one
for Microsoft DAO 3.6 Object Library, and select it. If you're not going
to be using ADO, uncheck the reference to Microsoft ActiveX Data Objects
2.1 Library

If you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO
recordset, you'll need to use Dim rsCurr as DAO.Recordset (to guarantee
an ADO recordset, you'd use Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property,
Properties and Recordset


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)




I recently imported all my data into a blank DB now I am getting this
error on:
Dim wrk As Workspace
Is that something to do with me importing into a new DB
Thanks for any help...........Bob

Sub SelAllNone(Optional SelectAll As Boolean = True)
On Error GoTo stoprun

Dim sqlStr As String
Dim wrk As Workspace
Dim db As Database

Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb


sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";"
wrk.BeginTrans
db.Execute sqlStr, dbFailOnError
wrk.CommitTrans

Exit_Here:
Set wrk = Nothing
Set db = Nothing
Exit Sub

stoprun:
MsgBox Err.Number & " - " & Err.Description
wrk.Rollback
Resume Exit_Here
End Sub

 

[Tom Wickerath]

Hi Bob,

Without it ticked I am getting an all clear Debug/Compile, should I just not
tick the 2.7

You should not tick it unless it is needed. You've just proven to yourself
that you do not need it, since you are getting the "all clear". So, if you
tick this reference, you'll only be needlessly loading this library into RAM
memory, but, it will never be used.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________