Error When set form at Startup

[Ann B]

I have made a copy of a working db -to shrink its size- everything goes
well until I set the User ID form at the Startup. Then when I close and
re-open I get this error in the File List Module- It is a compile Error-
invalid use of New Keyword. I am confused as it works when it is set this
way before I copied the Tables, queries, and modules into the new blank
database. Help!! I have to shrink this for our customer. It stops on the
DIM of the conflict table.

Option Compare Database

Public Function MyResolver()
Dim Db As Database
Dim Td As TableDef
Dim ConflictTable As New Recordset
Dim TableName As String
Dim SQLstr As String
Dim i As Integer
Set Db = CurrentDb
' Step backward through the TableDefs collection so you
' do not miss any tables when you delete conflict tables.
For i = Db.TableDefs.Count - 1 To 0 Step -1
Set Td = Db.TableDefs(i)
If (Td.ConflictTable <> "") Then
TableName = Td.ConflictTable
SQLstr = "DELETE * FROM [" & TableName & "];"
ConflictTable.Open SQLstr, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

DoCmd.DeleteObject acTable, TableName
'Td.ConflictTable = ""

' Open a recordset based on the conflict table.
' Insert code to do conflict resolution.
' Delete the conflicting record when you are done.
' Delete the conflict table when all its records are deleted.
' Set the ConflictTable property to "".
End If

 

[SusanV]

You should dim recordsets as either Adodb.Recordset or DAO.Recordset - in
this case you are using DAO recordsets, and Access does not by default have
a reference to the Microsoft DAO Object Library. Add this reference and
hopefully your problem will be solved.

 

[Ann B]

Thanks Susan, I thought I had done this...but I double checked and
re-arranged the order of the References and it worked. You are a lifesaver.

You should dim recordsets as either Adodb.Recordset or DAO.Recordset - in
this case you are using DAO recordsets, and Access does not by default have
a reference to the Microsoft DAO Object Library. Add this reference and
hopefully your problem will be solved.

--
hth,
SusanV

I have made a copy of a working db -to shrink its size- everything goes
well until I set the User ID form at the Startup. Then when I close and
re-open I get this error in the File List Module- It is a compile Error-
invalid use of New Keyword. I am confused as it works when it is set this
way before I copied the Tables, queries, and modules into the new blank
database. Help!! I have to shrink this for our customer. It stops on
the
DIM of the conflict table.

Option Compare Database

Public Function MyResolver()
Dim Db As Database
Dim Td As TableDef
Dim ConflictTable As New Recordset
Dim TableName As String
Dim SQLstr As String
Dim i As Integer
Set Db = CurrentDb
' Step backward through the TableDefs collection so you
' do not miss any tables when you delete conflict tables.
For i = Db.TableDefs.Count - 1 To 0 Step -1
Set Td = Db.TableDefs(i)
If (Td.ConflictTable <> "") Then
TableName = Td.ConflictTable
SQLstr = "DELETE * FROM [" & TableName & "];"
ConflictTable.Open SQLstr, CurrentProject.Connection,
adOpenKeyset,
adLockOptimistic

DoCmd.DeleteObject acTable, TableName
'Td.ConflictTable = ""

' Open a recordset based on the conflict table.
' Insert code to do conflict resolution.
' Delete the conflicting record when you are done.
' Delete the conflict table when all its records are deleted.
' Set the ConflictTable property to "".
End If

 

[SusanV]

Glad to help any time
;-D

Thanks Susan, I thought I had done this...but I double checked and
re-arranged the order of the References and it worked. You are a
lifesaver.

You should dim recordsets as either Adodb.Recordset or DAO.Recordset - in
this case you are using DAO recordsets, and Access does not by default
have
a reference to the Microsoft DAO Object Library. Add this reference and
hopefully your problem will be solved.

--
hth,
SusanV

I have made a copy of a working db -to shrink its size- everything
goes
well until I set the User ID form at the Startup. Then when I close
and
re-open I get this error in the File List Module- It is a compile
Error-
invalid use of New Keyword. I am confused as it works when it is set
this
way before I copied the Tables, queries, and modules into the new blank
database. Help!! I have to shrink this for our customer. It stops on
the
DIM of the conflict table.

Option Compare Database

Public Function MyResolver()
Dim Db As Database
Dim Td As TableDef
Dim ConflictTable As New Recordset
Dim TableName As String
Dim SQLstr As String
Dim i As Integer
Set Db = CurrentDb
' Step backward through the TableDefs collection so you
' do not miss any tables when you delete conflict tables.
For i = Db.TableDefs.Count - 1 To 0 Step -1
Set Td = Db.TableDefs(i)
If (Td.ConflictTable <> "") Then
TableName = Td.ConflictTable
SQLstr = "DELETE * FROM [" & TableName & "];"
ConflictTable.Open SQLstr, CurrentProject.Connection,
adOpenKeyset,
adLockOptimistic

DoCmd.DeleteObject acTable, TableName
'Td.ConflictTable = ""

' Open a recordset based on the conflict table.
' Insert code to do conflict resolution.
' Delete the conflicting record when you are done.
' Delete the conflict table when all its records are
deleted.
' Set the ConflictTable property to "".
End If