Recordsets. I can not make them work. Can you examine this code please?

[graffiti]

Hello again Accessors.



I am struggling with recordsets. I believe I understand what they are but the examples I have researched are very inconsistant. They're written differently from each other and include and miss out different things from each other. The explanatory text is usually basic and leaves me feeling dumber each time I read them.

1. I shall be connecting to tables both inside and outside the original .mdb application, just to be awkward. Therefore I believe I need to use ADO for this. I have set it in my tools / references as higher in the list than dao.

2. Some online examples show the routine opening a connection before recordsets etc. are used. Do I do this even whan accessing table within the original .mdb? Do I do this once at the beginning of the program and leave it open or do it each time a subroutine that accesses the external table is called? I suppose I need to know the scope of an open connection.

3. On a button click, I wish to copy the content of a field in table 1 to a field in a new record, a log file, in table 2. (These are local not external tables.) Do I need to open a record set for each table then copy between them? Or open a recordset for the destination table and get the data from the originating table, how?

4. I am an absolute newbie trying to learn using online snippets from Google searches! (Hardly the way to get a qualification, but it's what I've got!) I don't want to waste my or your time, so can anyone tidy up / correct the below code please? I know it's pants, but I'm not sure why! Can you explain a bit of detail about the correct way of doing this? Once I understand it better I hope I won't have to keep asking stupid questions.

 
'Initialise
  ' Allocate a block of memory for the db
  Dim db As Database
  ' Allocate a block of memory for the recordset
  Dim rsetUsers As Recordset
  'Point db to the current database
  Set db = CurrentDb
  'Open the table "tblLoggedOnUser" for reading and writing.
  rsetUsers.Open "tblLoggedOnUser", , adOpenDynamic, adLockOptimistic
  'This is (I think) the DAO version?
  'Set rsetUsers = db.OpenRecordset("tblLoggedInUser")
 
'Add new record to tblLoggedInUser
  rsetUsers.AddNew
 
'Copy the info from tblDBUsers to make a log of the visit
  'rsetUsers!ID = ??
 
'Clean up memory
  rsetUsers.Update
  rsetUsers.Close
  Set rsetUsers = Nothing
  Set db = Nothing

"Object variable or With block variable not set"

Any light that you can shine will be gratefully recieved. Thank you.

Mike. (UK)

[RuralGuy]

You are using predominently DAO code. I would strongly suggest you specify the Library for your Dim statements to eliminate any confusion.
Dim db As DAO.Database
Dim rsetUsers As DAO.Recordset
FYI...CurrentDB is DAO.

[graffiti]

Thanks for that info. My online searches for "ADO recordset" have not made sense so far but I'll have another look later today.

[John_G]

Hi -

Do you reall need ADO? Where are these external tables located? If they are in another Access .mdb database, you don't need "connections" - you can set them up as linked tables (File - Get External Data - Link Tables...). From that point, you can treat them just as you would any other local table.

Please tell us a bit more about what it is you are trying to do. I understand you want to keep a log of user visits (a common procedure) - but where is the name of the "current user" coming from? (or is that the question you need answered?!)

John

[graffiti]

Hi John.

I didn't include enough detail in my original message, so here is a bit more. Hopefully it'll make sense, but right now I'm taking someone els'es advice and reading up on the basic SQL append and other queries.

I work for a borough council here in the south of the UK. I'll explain the database itself further down but I will spend most of my time accessing data stored in a SQL server in our town hall. I'm at a remote site about 3 miles away and we have our own WAN. That's why I believe I will need ADO rather than DAO.

For those who are interested, here is an explanation of my db. One of my jobs is to collate information about our town's graffiti taggers. Our cleaners carry a blackberry phone with some software on it which they use to photograph and record each tag, before and after cleaning. This sends the picture to the SQL server in the town hall along with it's GPS location and some other fields which they fill out. The server uses the GPS reference to look up our gazateers and get the nearest recorded address. (For our purposes, this is good enough.)

Now I have to USE this info. To make it easier I'm creating an access database front end for the SQL database. As we are connected to the government, we are very tied down in who can access and manipulate what. I can only read / write the tables I'm allowed to, but I can't add fields or new tables. I have to get someone from IT to do that for me. They don't have the resources (only one db programmer in the council) to create more work at the moment, so I'm doing it the hard way.

So, the local tables will be stuff I can change, housekeeping stuff like logins and help files. The local tables will be a part of the .mdb file. 98% of the work will be on the SQL remote db accessing and manipulating the graffiti data.

What I need to actually do with the data is to examine the images by eye, write in a field I call TAG any details of what I can see, trying to be consistant. Then I shall need to see which is the most prevelant tags, within time frames or areas, see which tags are often seen together, use the GPS to show selected tags on a map using either Map info Pro or maybe Plan web. (These are our in house mapping systems. We're not allowed Google due to licence terms.) And other things as we think of them, such as reports on statistics, reports on individual taggers for police evidence and who knows what else. If this works then it may go out to other councils.

So, any advice on how to implement this would be really well recieved! Thanks.