Using the OpenRecordset method 1

[sera]

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 [sig][/sig]

 

[Robertd]

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 [sig][/sig]

 

[sera]

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 [sig][/sig]

 

[Robertd]

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 [sig][/sig]

 

[KirkJewell]

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
[sig][/sig]

 

[KirkJewell]

Bugger! the code example should read

dim rs as recordset

set rs = currentdb().dbopenrecordset(etc)

not 'rs as' as I have typed before (SORRY)
[sig][/sig]