asked on

Access 07 Relink Tables in a distributed Split DB

Hello All
Testing the splitting and distributing of a db. I used the Ac2007 Package wizard and then went to another computer I have and did an install (non networked) Well when I open the FE on that other computer it give the error message (can't find the BE). So after doing some reading here I tried some oldie but seems to be goldie solutions. However, this will be loaded on 32 and more likely 64bit machines.

I tried Dave Ashish's http://access.mvps.org/access/tables/tbl0009.htm

With that link I kept getting an error break on Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")

fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

So I figured it was bombing because I didn't have the API he referred to in his link labeled "GetOPenFileName"

SO, I got that function.... But The following code really is hated by 64 bit systems... Stops immediately and says it must be updated for 64bit.

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

So, does anyone have any VB for Relinking of a Split then distributed DB????????

als315

I've never used it, but it was recommended by Armen Stein:
Free J Street Access Relinker: www.JStreetTech.com/downloads
You need add declaration for 64 bit systems. You can find sample here:
http://eileenslounge.com/viewtopic.php?f=29&t=4833
Here you can find some theory and examples:
http://msdn.microsoft.com/en-us/library/ff700513.aspx#odc_dnofftalk_ta_WorkingwithVBA32bit64bitOffice2010_Workingwith32bit64bitOffice2010

wlwebb

ASKER

Als315
THANK YOU!
However, I read the link and tried adding the If function for a 64 bit based on the way I "understand" that HansV was suggesting so code could work in both 32 and 64

Here is what I modified Ken Getz's to but I still get the Error.....
Have I misunderstood something?

'***************** Code Start **************
' This code was originally written by Ken Getz.
' It is not to be altered or distributed, 'except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code originally courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996
' Revised to support multiple files:
' 28 December 2007

' *** With Modification for 64bit machines suggested by HansV
' ****** With Modification for 64bit machines suggested by HansV
#If VBA7 Then ' 64bit modification

Type tagOPENFILENAME  ' 64bit modification
  lStructSize As Long ' 64bit modification
  hwndOwner As LongPtr ' 64bit modification
  hInstance As LongPtr ' 64bit modification
  lpstrFilter As String ' 64bit modification
  lpstrCustomFilter As String ' 64bit modification
  nMaxCustFilter As Long ' 64bit modification
  nFilterIndex As Long ' 64bit modification
  lpstrFile As String ' 64bit modification
  nMaxFile As Long ' 64bit modification
  lpstrFileTitle As String ' 64bit modification
  nMaxFileTitle As Long ' 64bit modification
  lpstrInitialDir As String ' 64bit modification
  lpstrTitle As String ' 64bit modification
  flags As Long ' 64bit modification
  nFileOffset As Integer ' 64bit modification
  nFileExtension As Integer ' 64bit modification
  lpstrDefExt As String ' 64bit modification
  lCustData As Long ' 64bit modification
  lpfnHook As LongPtr ' 64bit modification
  lpTemplateName As String ' 64bit modification
End Type ' 64bit modification

Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" _
  Alias "GetOpenFileNameA" (OFN As OPENFILENAME) As Boolean

Declare PtrSafe Function GetSaveFileName Lib "comdlg32.dll" _
  Alias "GetSaveFileNameA" (OFN As OPENFILENAME) As Boolean

#Else ' 64bit modification
' ******** End  64bit modification With addition of "End If" at end of this section ****


Type tagOPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    strFilter As String
    strCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    strFile As String
    nMaxFile As Long
    strFileTitle As String
    nMaxFileTitle As Long
    strInitialDir As String
    strTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    strDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Private Declare Function GetOpenFileName Lib "comdlg32.dll" _
  Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Private Declare Function GetSaveFileName Lib "comdlg32.dll" _
  Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean

#End If

'Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
'    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

'Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
'    Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
'Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000

Open in new window

als315

I have no now 64-bit system for testing, try this modification
JStreetAccessRelinker.mdb

Armen Stein - Microsoft Access MVP since 2006

Hi als315,

Thanks for the mention of our J Street Access Relinker. And thanks for posting the 64-bit compatible version. I haven't had a chance to try it out in both environments, but perhaps wlwebb will report back.

We haven't had time to test and post the 64-bit compatible code for it - we're busy with client work, and we don't recommend that our clients use 64-bit Office anyway. Hopefully we will be able to post an update soon.

Cheers,
Armen Stein

wlwebb

ASKER

Armen

I have not yet tested it but do have a question.

You indicate in your post that you don't recommend client use 64bit Office. Since I am a fairly obvious novice can you explain that comment? IE: what are the downsides or limitations and what are the upsides and positives.

I am curious.

Armen Stein - Microsoft Access MVP since 2006

Hi wlwebb,

Well, it isn't just me. Microsoft recommends the 32-bit version also:
http://office.microsoft.com/en-us/word-help/choose-the-32-bit-or-64-bit-version-of-office-HA010369476.aspx

"The 32-bit version of Office 2010 is the recommended option for most people, because it prevents potential compatibility issues with other 32-bit applications, specifically third-party add-ins that are available only for 32-bit operating systems."

The main reasons to avoid 64-bit are compatibility issues, requiring code changes and problems with controls. However, there are some advantages with 64-bit for Excel users, and many people just think 64 must be twice as good as 32, so some people definitely install it. So it is good to know how to deal with 64-bit Office.

In our case, most of our clients seek our advice on what to install, so we recommend 32-bit Office to keep our projects simpler.

Cheers,
Armen

Armen Stein - Microsoft Access MVP since 2006

And remember, none of this has to do with the version of Windows. We run 64-bit Windows 7 all the time. It's great.

Armen

wlwebb

ASKER

Armen
Thanks! Another question.... Have you downloaded the Office 2013 beta yet and done any testing? Right now it looks like there will still be a 32bit version of 2013 have you heard any different.

Armen Stein - Microsoft Access MVP since 2006

We haven't done extensive testing on 2013 yet, as we like to wait until it is stable before we use it on client projects. :) But as an MVP I've been monitoring its progress for quite a while, and I'm pleased with the new web + SQL Server capabilities. The desktop client side doesn't have many improvements, but most of the existing features are supported.

Yes, there are both 32-bit and 64-bit versions of Office 2013. Microsoft's recommendation to stick with 32-bit still stands:

http://technet.microsoft.com/en-us/library/ee681792.aspx

http://officepreview.microsoft.com/en-us/support/choose-the-32-bit-or-64-bit-version-of-office-HA102840825.aspx

Cheers,
Armen

wlwebb

ASKER

Armen,
Just tried the relinker on 32 bit.... worked like a Charm!....SO LONG as you make the directory a Trusted Directory.... if it isn't a "Trusted Directory" it stops the Macro.

Have to fire up the 64 bit to test there

Armen Stein - Microsoft Access MVP since 2006

Hi wlwebb, thanks for the update. A trusted location is needed to run any code, not just ours!

als315

Code was tested on:
Windows 7 x32, Office 2010 x32 - OK
Windows 7 x64, Office 2010 x32 - OK
Windows 7 x64, Office 2010 x64 - Error (File dialogue was not opened)

als315

Seems there is problem with GetOpenFile in Office x64. I've changed function to FileDialog. Now it should work in all versions of Office.
JStreetAccessRelinker.accdb

wlwebb

ASKER

Yes I got error in 64bit. Errored out and kicked me out of Access. Won't open at all

wlwebb

ASKER

Als315

When I select your 64 bit fix all it does is open a window with a bunch of jibberish and symbols. It doesn't open a download option. Any way for me to fix that?

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

I have deleted the complete 64 bit and reinstalled 32 bit

wlwebb

ASKER

Only trouble was with 64bit