Easy question: .AddNew in Recordset.

[I'm a Trampoline]

The following doesn't work. It gets hung up on the .AddNew step.

********************
Set preferencesRecordset = CurrentDb().OpenRecordset("SELECT * FROM
Preferences;")
If preferencesRecordset.RecordCount = 0 Then
With preferencesRecordset
.AddNew
.Edit
![DO FIELD STUFF IN HERE]
.Update
End With
End If
********************

Also, is there a reason why I can only get the recordset to work with
SQL, I would have thought I could have just had ("Preferences",
dbOpenTable) after OpenRecordset, but that doesn't seem to want to work.
I'm just using jet.

Thanks to anyone that can help.

 

[Dirk Goldgar]

The following doesn't work. It gets hung up on the .AddNew step.

********************
Set preferencesRecordset = CurrentDb().OpenRecordset("SELECT * FROM
Preferences;")
If preferencesRecordset.RecordCount = 0 Then
With preferencesRecordset
.AddNew
.Edit
![DO FIELD STUFF IN HERE]
.Update
End With
End If
********************

The methods .AddNew and .Edit are mutually exclusive. .AddNew is for
adding a new record, and automatically puts that record in "edit mode".
..Edit is for editing and existing record. So if you're just trying to
add a record, you should drop the call to .Edit.

Also, is there a reason why I can only get the recordset to work with
SQL, I would have thought I could have just had ("Preferences",
dbOpenTable) after OpenRecordset, but that doesn't seem to want to
work. I'm just using jet.

I assume "Preferences" is the name of a table, not a query. But is it a
linked table? If so, you can't use dbOpenTable. Did you try using

Set preferencesRecordset = _
CurrentDb.OpenRecordset("Preferences", dbOpenDynaset)

or just

Set preferencesRecordset = _
CurrentDb.OpenRecordset("Preferences")

? The latter will open a table-type recordset for local tables, and
dynaset for linked tables.

 

[I'm a Trampoline]

The following doesn't work. It gets hung up on the .AddNew step.

********************
Set preferencesRecordset = CurrentDb().OpenRecordset("SELECT * FROM
Preferences;")
If preferencesRecordset.RecordCount = 0 Then
With preferencesRecordset
.AddNew
.Edit
![DO FIELD STUFF IN HERE]
.Update
End With
End If
********************

The methods .AddNew and .Edit are mutually exclusive. .AddNew is for
adding a new record, and automatically puts that record in "edit mode".
.Edit is for editing and existing record. So if you're just trying to
add a record, you should drop the call to .Edit.

Also, is there a reason why I can only get the recordset to work with
SQL, I would have thought I could have just had ("Preferences",
dbOpenTable) after OpenRecordset, but that doesn't seem to want to
work. I'm just using jet.

I assume "Preferences" is the name of a table, not a query. But is it a
linked table? If so, you can't use dbOpenTable. Did you try using

Yes, it's a linked table. I should have remembered that. I'm sure you've
covered both questions adequately, thanks, I'll have to try at work
tomorrow (only have Macs at home).

One question pops to mind. I've noticed you can specify the path to an
external database in code, without utilising a linked table sitting
there under your tables tab. Is any way more preferable than the other?

I'm assuming with the earlier method, if you've allowed your users to
shift-open their MDE, that they won't get directly to any tables, which
could be good. If not, impressive to my workmates at least, "whoa, where
are the tables?" (We're all general computing tutors, the other wind
down by throwing beach balls around the office, I play with Access).

 

[Dirk Goldgar]

One question pops to mind. I've noticed you can specify the path to an
external database in code, without utilising a linked table sitting
there under your tables tab. Is any way more preferable than the
other?

From a performance point of view, I don't know for sure. It may be that
Access handles the connection to the database containing linked tables
better than one created on the fly by the IN clause of a query.
However, I don't know this to be the case. It could well be that the
database engine doesn't care, and simply caches connections as they are
opened, regardless of how they are specified.

There is a practical advantage to using linked tables, though: it's
much easier to maintain your database in cases where the back-end data
store has been moved. You need only use the Linked Table Manager (or
your own equivalent code) to go through the linked tables and respecify
the path to the back end. You don't need to modify stored queries, and
you don't need to modify code that builds SQL statements on the fly. So
I'd be inclined to go with linked tables unless there's a good reason to
do otherwise.

I'm assuming with the earlier method, if you've allowed your users to
shift-open their MDE, that they won't get directly to any tables,
which could be good. If not, impressive to my workmates at least,
"whoa, where are the tables?" (We're all general computing tutors,
the other wind
down by throwing beach balls around the office, I play with Access).

Well, yes, you coud hide your tables that way, but I don't really see
the point. If you don't want people poking around in your tables you
can use workgroup security on the back-end to prevent that, allowing
only your own queries to access the tables.

 

[Pat Hartman]

Permanent links are faster than using the IN clause in your queries. When a
table is first linked, Jet obtains information about the linked database and
the specific table. This information is stored so that it can be used when
the table is accessed. When you use the IN clause, this information
exchange needs to happen prior to each time the query is run. This causes
unnecessary overhead and has a negative effect on the speed of the
application.