In case anyone is interested the reason my code wouldn't work was that I
didn't declare which type of recordset I was using in my Dim statement, so
Access VBA assumed that it was an ADO recordset, rather than DAO (and I was
using a DAO Database object...). Maybe this was because the reference to
Microsoft ActiveX data objects was before that to DAO...? or maybe I don't
know what I am talking about. Anyway changing this:
toQuote:> Dim rsMaxPreviousID As Recordset
Dim rsMaxPreviousID As DAO.Recordset
Quote:> Hi,
> I an trying to make a form that connects to data in a replicated database.
> Following what I believe to be good design guidelines for this kind of
thing
> I am trying to split the frontend and backend. I need to retrieve data
from
> the table my form is connecting to, and I could do this fine by setting a
> database object to Currentdb, when my form was in the same database as the
> table.
> Now that I have exported the form into a database that uses linked tables
I
> am unable to get the *y data from the table. I thought I would be
able
> to use the OpenDatabase method but I can't get it to work. I want to
> connect to the db in the simplest way possible and I definitely don't want
> to use a system DSN.
> I am a bit of a newbie to this sort of stuff, so if someone can suggest a
> better way to achieve this I would be greatful. The error message I am
> getting with my current code is a type mismatch. The database object
seems
> to be created ok, it's just wont open a recordset...it seems the
> openrecordset method is returning a non recordset type??? I don't know
> what's happened and am at the tearing my hair out stage....
> Can anyone help....please...
> Private Sub NewRecordInitialisation()
> Dim objdb As Database, objConnection As New ADODB.Connection
> Dim intIDNum As Long, strSQLForMaxID As String
> Dim rsMaxPreviousID As Recordset
> Dim objUsersTable As TableDef
> 'Set objdb = CurrentDb
> strFullPath = CurrentProject.Path & "\" & "malls2000.mdb"
> Set objdb = OpenDatabase(strFullPath)
> strSQLForMaxID = "SELECT MAX(ID) FROM Users WHERE (ID > 70000) AND (ID
<
> 99999)"
> Set rsMaxPreviousID = objdb.OpenRecordset(strSQLForMaxID)
> If Not (rsMaxPreviousID.EOF) Then
> rsMaxPreviousID.MoveFirst
> If IsNull(rsMaxPreviousID) Then
> intIDNum = 70001
> Else
> intIDNum = rsMaxPreviousID(0) + 1
> End If