VBA Code won't work with linked tables

[Seiquo]

Hello all,

I found the following code to delete then insert records in a local table in a front file. (Context: it allows users to selects rows in a form by checking a box on each row, then adds the slected rows to a table so they can do whatever they have to do with the said selected rows):

Sub InitialiserSelection( _
  ByVal strTable As String, _
  ByVal strPrimaryKey As String, _
  Optional ByVal strTableSelection As String = "Tb_Selection", _
  Optional ByVal strWhere As String = "")
  
  strTableSelection = "[" & strTableSelection & "]"
  CurrentDb.Execute "DELETE * FROM " & strTableSelection & ";"
  
 Dim strSQL As String
  strSQL = "INSERT INTO " & strTableSelection & " (Numero, Selection)" _
    & " SELECT [" & strPrimaryKey & "], False" _
    & " FROM [" & strTable & "]"
  If strWhere <> "" Then strSQL = strSQL & " WHERE " & strWhere
  CurrentDb.Execute strSQL
End Sub

My problem is that it works perfectly with local tables but once the DB project is split with only TbSolution being converted to a local table in the front file, then it will no longer work. I found out that it works only if BOTH the TbSelection table AND the second table from which the items are selected are local (which I don't want because my second table is related to another linked table).

Does anyone have any idea why this code won't work with linked tables?

Thank you in advance.

[isladogs]

A few things:

Your code doesn't define which table is referenced by strTable
It also defines strTableSelection in a rather clunky two step manner which could be streamlined.

Is the external table linked to the main db? Is the backend db password protected?

[Minty]

What type of linked table and what error message?

[Seiquo]

Hello ridders52 and Minty!

Thank you for sparing some of your time!

To ridders52:

1) Should I correct the first line for:

ByVal strTable As String = "Tb7_Projet", _

I tried it but Access gives me the following message: "Compile error: Expected separator or )"

2) Would you be kind enough to explain how to simplify the TableSelection definition?

2) All the linked tables are in a .be file. There is no password.

To Minty:

I don't get any error message. The form is simply empty. But as soon as I convert my Tb7_Projet to a local table, everything works fine.

I am sure I am not so far from a happy ending but I am not that familiar with VBA syntax et that is why I am seeking some help

[isladogs]

I've made a couple of minor changes to your code & tested it with a linked database table.
I've removed specifying the destination table from the procedure declaration
Also removed all [] which you only need if you have spaces in the table names
It works for me - see attached

Function InitialiserSelection(ByVal strTable As String, _  ByVal strPrimaryKey As String, _
  Optional ByVal strTableSelection As String, _
  Optional ByVal strWhere As String = "")
  
 ' strTableSelection = "[" & strTableSelection & "]" 'NOT NEEDED UNLESS YOU HAVE SPACES
  CurrentDb.Execute "DELETE * FROM " & strTableSelection & ";"
  
  Dim strSQL As String
  'strSQL = "INSERT INTO " & strTableSelection & " (Numero, Selection)" _
    & " SELECT [" & strPrimaryKey & "], False" _
    & " FROM [" & strTable & "]"
    
    'None of the [] brackets are needed UNLESS YOU HAVE SPACES
    strSQL = "INSERT INTO " & strTableSelection & " (Numero, Selection)" _
    & " SELECT " & strPrimaryKey & ", False" _
    & " FROM " & strTable & ""


 ' Debug.Print strSQL
  
  If strWhere <> "" Then strSQL = strSQL & " WHERE " & strWhere
  CurrentDb.Execute strSQL
End Function

I've then run this sub from a form button

Sub TestCode()
    InitialiserSelection "Tb7_Projet", "ID", "Tb_Selection"
End Sub

[Micron]

None of the [] brackets are needed UNLESS YOU HAVE SPACES

Or Heaven forbid, start a field name with a number. I wouldn't have known that if it weren't for helping out someone else here who needed to read up on how to name things.

[isladogs]

Or Heaven forbid, start a field name with a number. I wouldn't have known that if it weren't for helping out someone else here who needed to read up on how to name things.

Or of course if you have special characters like ? # % in table or field names or use a reserved Access word.
In one case recently a user managed all of those in the same query together with half a dozen syntax errors.
After all, why make life easy as a developer when you can make it unnecessarily hard.

[Seiquo]

Good morning ridders52 and Micron,

To ridders52:

So....... I am sorry to say that I get the same thing: empty form.

I opened your test file and found that yours is different from mine: there is a query and a form missing. I properly re-linked your table and explored the modules and VBA code. I tested in different ways: before fractionning, after, before creating the accde file, after... Always the same: works with both involved tables as local, but not if the source table is linked.

I'm currently working on creating a "anonymous" Access file to upload so you can have a look at the exact process. I will leave only the related tables, queries and forms involved and I hope you will be able to put your finger on the problem. I may be able to create this file today, or this weekend.

I am pretty sure it's "nothing". And my project will be completed as soon as I find the solution.

Thank you again for you help and I hope you will agree to take a look at my DB when it's ready.


Micron: good reminder, thank you!

Cheers.

[isladogs]

Sorry but I've no idea what you mean
You've not shared your database so far so I have no idea what it contains

My example contains a very simple form which has a button that runs the code I posted in #5



The code deliberately uses the same idea as yours with only slight modifications to make it work better (in my view)
There was no query as it didn't need one and anyway you didn't include one

Are you saying you get a blank form when you open my database?
If so, I think you need to run an Office repair as it suggests that may be your problem

[Seiquo]

No I don't get any blank form. I see exactly the same thing as in your printscreen.

I am attaching a copy of my DB. You just have to follow the "Click here" buttons in red

I left almost everything (even the password window!!) so you have all the cards in hand.

I fractionned the file, then created the accde file, exactly like I will need to procede when the **** thing works (you can replace **** with "beautiful", "useful", or whatever word you want - mine is already chosen...).

So the Zip file contains Seiquo_Test fractionned and "accde-ed", and Seiquo_Test2 intact. And forgive me in advance but I do not have the time to translate all the labels and controls in all the forms...

I am crossing my fingers...

Did I say thank you already? Thank you anyway.


Edit: Oops... I forgot your Access is most certainly in English... and my queries are written in French. Please let me know if there is any problem and I'll rewrite them if need be.

[isladogs]

Ok so what did you mean by empty form?

I'm very busy at the moment but will try to look at it later tonight or tomorrow.

I won't be able to do anything with accde files so please upload the accdb versions
To save me time, please remove everything not relevant to the issue you're describing.
Do I need the password stuff? If not, remove that as well.

And despite my terrible English in the previous post, that is my first language!
So I'd also appreciate you translating whatever you can as you suggested

[Seiquo]

If you can't work with accde files, then discard it. You can use the Seiquo_Test2 file (in the zip file) that is "whole".

Don't worry about time. I am aware that you are helping me and that you have a life, so no pressure

Normally, with the files I uploaded, you can't miss what I am trying to explain. So I am leaving the zip file as it is, just in case you want to have a look today, but I will try to make a "cleaner" file this afternoon or this evening (I already removed some forms, the reports and some queries but I can remove tables as well and check my queries for any French criteria that could confuse an English Access application).

I am sorry to say that I get the same thing: empty form.

I was saying that even with your corrections (which I greatly appreciate by the way!), I still get a blank selection form (the form where users check boxes to select projects) if I don't convert my project table to a local table.

Have a good rest of the day, sincerely.

[Seiquo]

Here is the cleaned file. I translated my queries criteria so you should not have any surprise. Let me kow if any!

Thank you!

[Micron]

Some issues in case you're playing with this, Colin. I opened first the linked file, but had to re-link to where I put the be. Got a parameter prompt for a form control, so I went into design and found that one query field had the French word for forms in it while the others didn't. Having seen this behaviour before, I changed the word to Forms! and made progress. Not clear, but I guess you have to make some combo selections because when I just click on the button, there's no visible reaction. Found that the form that should be visible is opening way off my screen area, so it was "invisible". However, this form (Select Project) is opening with data while the red label says it doesn't, so I don't know what to look for next. I guess I could try this again, but start with the split version.

[Seiquo]

Hello Micron,

Thank you for your participation

Did you use the Seiquo_TestDB file? I corrected the criteria for "Forms". Maybe you used the first file that I removed.
No, you don't need to select anything in the comboboxes at this point. You just have to click on the "Click here" buttons.
The Select Project form works when BOTH Tb_Selection and Tb7_Projet are local. So please fraction the DB, and only convert the Tb_Selection table to a local table. Then you will see that the Select Project form is blank. I put the red label to explain that it is this form that is a trouble maker.

Does that help you understand my problem?

Edit: you're right, I forgot to translate in a criteria. Done. However, it is not what is causing trouble in this case.

I am attaching the zip file with the corrected criteria.