I'm confused (1 Viewer)

swell

Member
Local time
Today, 23:48
Joined
Mar 10, 2020
Messages
77
Thank you sxschech.
I will try this, can see a couple things I am going to have to learn. :)
I have never written code in access as a "function" procedure a new learning path:giggle: keep me occupied while I am locked away from all the COVID-19 creatures out there !
Where do the last lines of code get utilized? I can't see any calls to those functions.

TIME PASSES.. watched 1 youtube on functions and can sort of see how this works :coffee::unsure:
 
Last edited:

swell

Member
Local time
Today, 23:48
Joined
Mar 10, 2020
Messages
77
sxschech
Thought the code was working when I copied the FE and BE to a different location but then when I deleted the old BE, I found it wouldn't work.
I used msgbox to try to figure out what was occurring .
The problem seems to be in this portion of code.
'get tables in back end database
If InStr(dbPath, "BackEndDb.accdb") > 0 Then
stsql = stsql & " AND left(Name,6) <> 'mSPLIT'"
End If
Set rs = CurrentDb.OpenRecordset(stsql)
If Err <> 0 Then Exit Function[/CODE]
when I set [ICODE
Set rs = CurrentDb.OpenRecordset(stsql)
MsgBox " err = " & Err
If Err <> 0 Then Exit Function
MsgBox " if not here then an error "

The "err" is 3024 which I believe is file not found so it seems to me that "CurrentDb" is pointing to the wrong address ?
I don't know how to get a look at "currentDb.OpenRecordset(stsql)".

"Instr" has the correct full path.

I was thinking (What if I substituted "BackEndDb.accdb") for "CurrentDb" ?
Do you think thats what should happen?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 23:48
Joined
Sep 21, 2011
Messages
14,048
You were supposed to change "BackEndDb.accdb" to the name of your DB ? :(

Put this in the open event of your form and change BackEndDb to the name of your Backend file (name portion only as the path is taken care of by FilePath)
 

sxschech

Registered User.
Local time
Today, 16:48
Joined
Mar 2, 2010
Messages
791
Sorry didn't have a chance to respond sooner, anyway, Gasman is correct, you need to replace BackendDB with the name that you are using for that. Maybe I should have worded it "yourBackendDB" to help indicate that this should be changed. Also, this part of the code (below) isn't necessary, but left it in as an example of a case where I needed to exclude a particular table from being relinked from the backend. So you can comment out or leave as is after changing the backend to your file name.
Code:
If InStr(dbPath, "BackEndDb.accdb") > 0 Then
stsql = stsql & " AND left(Name,6) <> 'mSPLIT'"
End If
If you are still having issues, let us know.
 

swell

Member
Local time
Today, 23:48
Joined
Mar 10, 2020
Messages
77
Sorry didn't have a chance to respond sooner, anyway, Gasman is correct, you need to replace BackendDB with the name that you are using for that. Maybe I should have worded it "yourBackendDB" to help indicate that this should be changed. Also, this part of the code (below) isn't necessary, but left it in as an example of a case where I needed to exclude a particular table from being relinked from the backend. So you can comment out or leave as is after changing the backend to your file name.
Code:
If InStr(dbPath, "BackEndDb.accdb") > 0 Then
stsql = stsql & " AND left(Name,6) <> 'mSPLIT'"
End If
If you are still having issues, let us know.
See my response to Gasman. In correcting the code - I found I had a typo in myBackEndDB name that compounded the debugging :cry:
After correcting the typo and the code the link now points to the path where I was debugging the code. Looks great and I believe it will do just what is required for this application. (y)
One thing that came to my attention is that this procedure gets done every time my main menu loads, there are a number of events on this menu that close the main menu and then open the main menu when they are done. I'm feeling I need to put a "front end" menu and do the linking there, I am wanting to add an auto backup procedure and maybe I could incorporate that in the "front end" menu also.:unsure:
Any thoughts along this line?
While I have your attention in the reference to the Backend I made a couple of changes which I think would make this procedure a little "portable"
Dim BeName "MyBackendDb name"
Call LinkTables(FilePath(CurrentDb.Name) & BeName)

and the same here
If InStr(dbPath, "BackEndDb.accdb") > 0 Then
If InsStr(dbPath, BeName) > 0


also made this mod

Dim BeName As String
Dim stPath As String

'not sure if I could pass dbpath directly without ramifications
stPath = dbPath

'get the back end file name from function FileNameWithExt

BeName=FileNameWithExt(stPath)


The only place that the BackendDb name has to be entered is in the call to the linktables function or is there a downside to this?
 
Last edited:

swell

Member
Local time
Today, 23:48
Joined
Mar 10, 2020
Messages
77
I have a different issue...
I created a startup form.. well modified one I copied from the net a few months ago.
basically it has an image on the page.... no click events.
an "on load" procedure to trigger the above code, an "on timer" procedure to close the form ( this works fine) and an "on close" procedure to open the main menu form (this works).
The on load procedure isn't being executed , I only discovered this while trying to learn how to use the debugger.
Found I couldn't put in a breakpoint and close the App and re-open with my break points, my break points disappeared, (since found out that that is normal)(btw I am using access 2013) I then set a msgbox and that wasn't/isn't being displayed.?
I saw that someone else was having this problem today but his form was more complex.

Hmmmm I went in to the options ... current database changed short cut menu closed and re-opened DB and now on load event triggers?
 
Last edited:

sxschech

Registered User.
Local time
Today, 16:48
Joined
Mar 2, 2010
Messages
791
Regarding post #47. The code I provided was portable and relies on the file name passed to it from the calling function/sub. The reason that there was a hard coded file name in the IF statement is because we needed to specifically exclude a table from being attached as we wanted to use a table with the same name that was stored in a "separate back end file" since this code relinked files from multiple databases.

For your other post #48, did you sort it out as you mentioned it "now triggers"
 

swell

Member
Local time
Today, 23:48
Joined
Mar 10, 2020
Messages
77
Regarding post #47. The code I provided was portable and relies on the file name passed to it from the calling function/sub. The reason that there was a hard coded file name in the IF statement is because we needed to specifically exclude a table from being attached as we wanted to use a table with the same name that was stored in a "separate back end file" since this code relinked files from multiple databases.

For your other post #48, did you sort it out as you mentioned it "now triggers"

Re your response to #47 , at the time I didn't understand the CurrentDB I have a little better understanding now, This application is my first time dealing with VBA.
In my past life I was a M/F technician, I did write a lot of code back then mainly in Cobol but Access VBA is a whole different fish :rolleyes:

Re #48 I decided to go to my backup copy of the DB just in case there was something "hidden" that caused this. I haven't had a problem since.
Basically the App is working fine , I am in the process of cleaning up and want to try to make some of my sub's more compact and portable.
Then I want to try to implement an autobackup of the BE.

Thank you for the code, I can see now I can remove that change I made in the Linktables function but will leave my change to the call as is as I like to code that way.
 

Users who are viewing this thread

Top Bottom