×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using the OpenRecordset method

Using the OpenRecordset method

Using the OpenRecordset method

(OP)
Hello,  
    I am new with coding in Visual basic.  I do know C++, but I don't know anything about Basic(other than I can read it and understand).  My database has been running slowly since I linked the tables to a back-end database.  I checked the Microsoft Access help files and they are telling me to  create an empty table in the linked database and link the table in the main database.  Then they say to use the OpenRecordset method to open the linked table.  I am guessing this isn't very hard since I can't seem to find useful references anywhere....I however am missing something, because what I am trying is not working.  Do you have any ideas?
Thank you,
Sera

RE: Using the OpenRecordset method

Hi Sera,

check the help system index under

<data access overview>

this explains how what and when, mostly!

although you should set your forms data source to queries
see previous reply.

HTH
Robert

RE: Using the OpenRecordset method

(OP)
Thank you Robert.  I think I need to do a little investigating because my data source for my forms is the tables.  I don't run queries in the forms, they are all quite simple and do not require calculations...I guess i'm not sure why I need to base the forms on queries.  Will this help my database run faster?
Thank you
Sera

RE: Using the OpenRecordset method

Hi Sera,

well actually you do have your forms based on queries, they are contained in the forms properties->data.recordsource, this gives you a dynaset (by default) for the forms data. generaly you should define your queries in the front end mdb with the tables residing in a seperate mdb file.
it can aid speed for simple db's but when the data sources get complicated (as they will) defined queries are much better!
 
as for speed there will be a slight degrading of preformance spliting the database, and a futher degrading by spliting the database accross a lan. but from what you described i would be looking at the indexing of the tables this is important when the tables grow in size.

also you shouldn't expect c++ speed it doesn't happen, but the performance won't be that different between single mdb and split mdb on the same machine.

Robert

RE: Using the OpenRecordset method

Hello Sera.

I have a BackEnd Database with about 20+ tables linked (over 80mb of data) and a frontend which does most of the work.

There is (as mentioned before) a decrease in performance when using linked tables (especially if there is a large number of records (94k+ in my case) in the BackEnd database.

However!!!

To open a table as a recordset use the following:-

' first set up a variable which will be used as a pointer (you'll be well aware of this principle from using C++)
(Please note the underscore means line continuation - you may not need to use it - but i can't fit it on one line here - and in your access code you MUST have a space before the '_')

dim rs as recordset

set rs = currentdb().openrecordset(strTableName,_ dbOpenDynaset)

N.B. Change the dbOpenDynaset (editable) to dbOpenSnapShot (read-only but slightly quicker).

If the table you wish to open a recordset pointer to is Local then you could also use dbOpenTable (which is quickest), however you CANNOT use dbOpenTable on linked tables.

Also (sorry to go on) you may have seen something like the following as an example code (MS Help Files are mostly reference and therefore useless 90% of the time), GET A 3rd PARTY BOOK.

dim db as database
dim rs as recordset

set db = dbengine(0)(0)
set rs = db.openrecordset(strTableName, dbOpenDynaset)

This is long winded try using currentdb() which does the db bit in one go.

i.e.

dim rs as recordset
set rs as currentdb().openrecordset(etc.)

P.S If you really want to degrade the performance of your r BackEnd/FrontEnd database try linking the BackEnd to another database. e.g. Database ->Backend -> FrontEnd

Feel free to email me directly about recordsets (including sending me the structure of your database.
kirkjewell@bizonline.co.uk

RE: Using the OpenRecordset method

Bugger! the code example should read

dim rs as recordset

set rs = currentdb().dbopenrecordset(etc)

not 'rs as' as I have typed before (SORRY)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close