Delete Command (1 Viewer)

[mane_uk]

Hi all,

I have a database splitted in Front End / Back End.

I am trying to delete some specific records ("activityID") using VBA code in every table that starts with "tblActivity". The code I am using is:

For Each tdf In db.TableDefs
   If Left$(tdf.name, 11) = "tblActivity" Then
       strDelete = "DELETE [" & tdf.name & "].* " & _
                 "FROM [" & tdf.name & "] " & _
                 "WHERE [" & tdf.name & "]" & ".[activityID] = " & intActivityID
 
       DoCmd.SetWarnings False
       DoCmd.RunSQL strDelete
       DoCmd.SetWarnings True
   End If
Next tdf

Although in the tdf.name the tblActivity appears when comes to run the Delete I have an error message:
Error 3078 - The Microsoft Jet database engine cannot find the input table or query "tblActivity"...

What am I doing wrong?

Probably good to mention that I don't have linked tables!!

Thanks

 

[DCrake]

I have a database splitted in Front End / Back End.


Probably good to mention that I don't have linked tables!!

A bit contradictory isn't it?

What does strdelete look like?

 

[mane_uk]

Hi DCrake, why contradictory? You can have the db as FE/BE with linked tables or, as in my case, doing everything through VBA. Having a FE/BE using VBA can't be said to be a splitted database?

Anyway, I managed to do it by using db.execute instead of docmd.runSQL

db.Execute( "DELETE [" & tdf.name & "].* " & _
                 "FROM [" & tdf.name & "] " & _
                 "WHERE [" & tdf.name & "]" & ".[activityID] = " & intActivityID)

Thanks anyway!!

 

[DCrake]

If you don't have any linked tables in your front end then how do you create queries etc. How are you populating forms?

 

[mane_uk]

Everything I do is via VBA.

So for example to add a new record in my table tblActivity_Contact I do:

    Dim dbContact As Database
    Dim rsContact As Recordset
    'open contact/hospitality table
    Set dbContact = openDatabase(sourceDb, False, False, passwordDB)
    Set rsContact = dbContact.OpenRecordset("tblActivity_Contact")
        
    'record new entrance
    rsContact.AddNew
    rsContact.Fields("activityID") = activityID
    rsContact.Fields("contactID") = contactID
    rsContact.Fields("dateCreated") = Now()
    rsContact.Update
        
    Set rsContact = Nothing
   dbContact.Close
   Set dbContact = Nothing

To update I need to search first so I would do something like

Set rsContact = dbContact.OpenRecordset("SELECT * FROM tblActivity_Contact WHERE ...")

I had to do this way as this database is being held in the network used by a few people and their machines are too slow so the linking was taking at least 30 minutes to be done... doing thing via VBA made it quicker!!

Cheers

 

[DCrake]

So I assume that all your forms are unbound?

 

[mane_uk]

yes, all unbound.

 

[DCrake]

That's how I work for 99% of the time.

 

[mane_uk]

Great minds think alike!!! hehehe!!

 

[boblarson]

Unbound forms, no linked tables??? Why use Access then? You just removed 99% of what it does. Sorry but it makes no sense to me whatsoever.

 

[mane_uk]

hi boblarson...

Ms Access is the only tool available here and does the work quite neat and well!! And having the problem with speed over the network and old laptops this is the best approach (unless you want to hang on 30 minutes while the tables are linked between front end and back end!!).

Just out of curiosity what tool would suggest then if not Ms Access to create a database? SQL?

Cheers

 

[boblarson]

Just out of curiosity what tool would suggest then if not Ms Access to create a database? SQL?

You could use SQL Server, SQL Server Express, Oracle, Oracle Express, MySQL (all of which are less prone - okay almost nill chance of - especially over a network). The Express editions are free and MySQL can be too, depending on your situation.

I'm surprised that your "SPEED" issue doesn't have just as much or more of a problem than using a linked table approach. Having to open a connection each time you want to store or retrieve data would, in my mind, take more time than having a linked table with a persistent connection. Also the development time for developing unbound forms as compared to bound forms is greater and more work involved. So basically when you do it that way you remove a huge amount of what Access already does for you and, in my mind, if you are going to do that, you might as well move to another database platform and use VB.NET or C#.NET (even the Express versions) to create a more secure frontend and backend.

 

[mane_uk]

I would have chosen myself going to the SQL route but the company I am working for only have Access available for development and we are not allowed to install or use express version even though they are free in some cases.
And I have to admit that although it might take a little bit longer to develop it is worthy it... the difference on comunication "speed" is notable!! The open and close connection run a lot faster than my linked tables!!
Anyway, this is the nice thing about Ms Access, that even though it might do a lot of the job for you as you mentioned, it still allows you to use code to do the same thing on a different approach!!