f



Opening Excel from Access

I have a form with a button which is supposed to open an Excel file (With 
lots of Macros /VBA) in it. The Excel file gets it's data from the Access 
program

Here is the code

Private Sub Storage_Click()

    On Error GoTo Err_Storage_Click

    Dim ExcelApp As Object
    Dim ExcelWasNotRunning As Boolean           ' Flag for final release
    Dim XLFilePath As String
    Dim XLName As String                        ' Excel file name from Paths
    Dim MyDb As Database
    Dim Msg As String

    ' Find the normal path
                                          ' Folder and File
    If Nz(ExcelPath) = "" Then
        ExcelPath = "C:\Storage.XLS"
    End If

    XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\")) 
' File Name

    If Dir(ExcelPath) <> XLName Then          ' Not found
        XLFilePath = FindFile("C:\Documents and Settings\Phil\My 
Documents\Access\MDB\WFYC\", XLName)
        Msg = "The name of the file you have selected is " & vbCrLf
        Msg = Msg & XLFilePath & vbCrLf
        Msg = Msg & "but the original file was " & vbCrLf
        Msg = Msg & ExcelPath & vbCrLf
        Msg = Msg & "Do you want to use the new name in future?"
        If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
            ExcelPath = XLFilePath
        End If
    End If

    Set ExcelApp = CreateObject("Excel.Application")

    If Err.Number <> 0 Then ExcelWasNotRunning = True
    Err.Clear    ' Clear Err object in case error occurred.

    ' Set the object variable to reference the file you want to see.

    Set ExcelApp = CreateObject("Excel.Application")
    ExcelApp.WorkBooks.Open ExcelPath, , True                   ' Read Only
    ExcelApp.Visible = True

Exit_Storage_Click:
    Exit Sub

Err_Storage_Click:
    If Err = 2447 Then              ' Corrupted File name (with # sign)
        Resume Next
    Else
        MsgBox Err.Description
        Resume Exit_Storage_Click
    End If

End Sub

When I run it, I get an Error Box "ODBC Microsoft Access Driver Login 
Failed" and the message "The database has been placed in a state by user 
'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or locked"

What am I doing wrong

Thank

Phil 


0
phil126 (320)
5/25/2008 1:40:17 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

27 Replies
833 Views

Similar Articles

[PageSpeed] 48

Sorry

Should have added

There is some code in the Excel application which refers to the Database and 
extracts the relevant information

No problem opening Excel whether the database is open or not.

Phil

"Phil Stanton" <phil@myfamilyname.co.uk> wrote in message 
news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
>I have a form with a button which is supposed to open an Excel file (With 
>lots of Macros /VBA) in it. The Excel file gets it's data from the Access 
>program
>
> Here is the code
>
> Private Sub Storage_Click()
>
>    On Error GoTo Err_Storage_Click
>
>    Dim ExcelApp As Object
>    Dim ExcelWasNotRunning As Boolean           ' Flag for final release
>    Dim XLFilePath As String
>    Dim XLName As String                        ' Excel file name from 
> Paths
>    Dim MyDb As Database
>    Dim Msg As String
>
>    ' Find the normal path
>                                          ' Folder and File
>    If Nz(ExcelPath) = "" Then
>        ExcelPath = "C:\Storage.XLS"
>    End If
>
>    XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\")) ' 
> File Name
>
>    If Dir(ExcelPath) <> XLName Then          ' Not found
>        XLFilePath = FindFile("C:\Documents and Settings\Phil\My 
> Documents\Access\MDB\WFYC\", XLName)
>        Msg = "The name of the file you have selected is " & vbCrLf
>        Msg = Msg & XLFilePath & vbCrLf
>        Msg = Msg & "but the original file was " & vbCrLf
>        Msg = Msg & ExcelPath & vbCrLf
>        Msg = Msg & "Do you want to use the new name in future?"
>        If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
>            ExcelPath = XLFilePath
>        End If
>    End If
>
>    Set ExcelApp = CreateObject("Excel.Application")
>
>    If Err.Number <> 0 Then ExcelWasNotRunning = True
>    Err.Clear    ' Clear Err object in case error occurred.
>
>    ' Set the object variable to reference the file you want to see.
>
>    Set ExcelApp = CreateObject("Excel.Application")
>    ExcelApp.WorkBooks.Open ExcelPath, , True                   ' Read Only
>    ExcelApp.Visible = True
>
> Exit_Storage_Click:
>    Exit Sub
>
> Err_Storage_Click:
>    If Err = 2447 Then              ' Corrupted File name (with # sign)
>        Resume Next
>    Else
>        MsgBox Err.Description
>        Resume Exit_Storage_Click
>    End If
>
> End Sub
>
> When I run it, I get an Error Box "ODBC Microsoft Access Driver Login 
> Failed" and the message "The database has been placed in a state by user 
> 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or locked"
>
> What am I doing wrong
>
> Thank
>
> Phil
> 


0
phil126 (320)
5/25/2008 3:59:21 PM
Please post the code that is in the Excel file.  If that code refers to the
database, then that is most likely where the error is occuring, in which
case, we might be better able to help you.


"Phil Stanton" <phil@myfamilyname.co.uk> wrote in message
news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> Sorry
>
> Should have added
>
> There is some code in the Excel application which refers to the Database
and
> extracts the relevant information
>
> No problem opening Excel whether the database is open or not.
>
> Phil
>
> "Phil Stanton" <phil@myfamilyname.co.uk> wrote in message
> news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> >I have a form with a button which is supposed to open an Excel file (With
> >lots of Macros /VBA) in it. The Excel file gets it's data from the Access
> >program
> >
> > Here is the code
> >
> > Private Sub Storage_Click()
> >
> >    On Error GoTo Err_Storage_Click
> >
> >    Dim ExcelApp As Object
> >    Dim ExcelWasNotRunning As Boolean           ' Flag for final release
> >    Dim XLFilePath As String
> >    Dim XLName As String                        ' Excel file name from
> > Paths
> >    Dim MyDb As Database
> >    Dim Msg As String
> >
> >    ' Find the normal path
> >                                          ' Folder and File
> >    If Nz(ExcelPath) = "" Then
> >        ExcelPath = "C:\Storage.XLS"
> >    End If
> >
> >    XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\"))
'
> > File Name
> >
> >    If Dir(ExcelPath) <> XLName Then          ' Not found
> >        XLFilePath = FindFile("C:\Documents and Settings\Phil\My
> > Documents\Access\MDB\WFYC\", XLName)
> >        Msg = "The name of the file you have selected is " & vbCrLf
> >        Msg = Msg & XLFilePath & vbCrLf
> >        Msg = Msg & "but the original file was " & vbCrLf
> >        Msg = Msg & ExcelPath & vbCrLf
> >        Msg = Msg & "Do you want to use the new name in future?"
> >        If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
> >            ExcelPath = XLFilePath
> >        End If
> >    End If
> >
> >    Set ExcelApp = CreateObject("Excel.Application")
> >
> >    If Err.Number <> 0 Then ExcelWasNotRunning = True
> >    Err.Clear    ' Clear Err object in case error occurred.
> >
> >    ' Set the object variable to reference the file you want to see.
> >
> >    Set ExcelApp = CreateObject("Excel.Application")
> >    ExcelApp.WorkBooks.Open ExcelPath, , True                   ' Read
Only
> >    ExcelApp.Visible = True
> >
> > Exit_Storage_Click:
> >    Exit Sub
> >
> > Err_Storage_Click:
> >    If Err = 2447 Then              ' Corrupted File name (with # sign)
> >        Resume Next
> >    Else
> >        MsgBox Err.Description
> >        Resume Exit_Storage_Click
> >    End If
> >
> > End Sub
> >
> > When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
> > Failed" and the message "The database has been placed in a state by user
> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or
locked"
> >
> > What am I doing wrong
> >
> > Thank
> >
> > Phil
> >
>
>


0
5/25/2008 6:43:18 PM
Thanks for coming back

Code is:-
Function GetAccess()
'
' Macro1 Macro
' Macro recorded 26/03/2008 by Phil Stanton
'
    Dim MDBName As String, DefaultDirectory As String, SQLStg As String

    On Error GoTo GetAccess_Err

    Worksheets("Linked Data").Activate
    With ActiveSheet
        MDBName = .Range("A1")

CheckFile:
        If Dir(MDBName) = "" Then                  ' Not found
            MDBName = Application.GetOpenFileName("Access Database ,*.mde", 
, "Where is the Club Database?")
            If MsgBox("Do you want to use this database in future?", 
vbQuestion + vbYesNo) = vbYes Then
                .Range("A1") = MDBName
            End If
        End If
    End With

'
    ' Clear Cells

    ActiveSheet.Range("A2:H300").Clear

    SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YPos, 
XLabelPosition, YLabelPosition, LabelAngle "
    SQLStg = SQLStg & "FROM `" & MDBName & "`."         ' Note the ` symbol. 
Significant

    DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))

    'DBName = "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
            '"ODBC;DSN=MS Access Database;DBQ=" & MDBName & 
";DefaultDir=C:\WFYC Data\Phil's Folder\Acces"
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=" & MDBName & ";DefaultDir=" & 
DefaultDirectory _
        ), Array("s\MDB;DriverId=25;FIL=MS 
Access;MaxBufferSize=2048;PageTimeout=5;")), _
        Destination:=Range("A2"))
        .CommandText = Array(SQLStg, "QSpaceAllocation QSpaceAllocation 
ORDER BY TypeOfSpace, Space")
        .Name = "Query from MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=True
    End With

    'Stop                ' Has it got here?

    Exit Function

GetAccess_Err:
Stop
    If Err = 1234 Then                            ' No Idea what the error 
code will be
        ThisWorkbook.Close , False
    Else
        MsgBox Err.Description
    End If

End Function

There is a lot of other code, but this is the bit where the data is 
"extracted" from Access.
As I said, no problem whether the Access DB is open or not, the problem 
comes when I click the button on Access form to show the Excel file.
The "Linked Data" worksheet holds and processes the data, and the output is 
11 labelled scatter charts, one on each page (Map of boat compound and where 
each boat is stored)

Thanks again

Phil


"ByteMyzer" <bNyOtSePmAyMzNeOr@ySaPhAoMo.com> wrote in message 
news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
> Please post the code that is in the Excel file.  If that code refers to 
> the
> database, then that is most likely where the error is occuring, in which
> case, we might be better able to help you.
>
>
> "Phil Stanton" <phil@myfamilyname.co.uk> wrote in message
> news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
>> Sorry
>>
>> Should have added
>>
>> There is some code in the Excel application which refers to the Database
> and
>> extracts the relevant information
>>
>> No problem opening Excel whether the database is open or not.
>>
>> Phil
>>
>> "Phil Stanton" <phil@myfamilyname.co.uk> wrote in message
>> news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
>> >I have a form with a button which is supposed to open an Excel file 
>> >(With
>> >lots of Macros /VBA) in it. The Excel file gets it's data from the 
>> >Access
>> >program
>> >
>> > Here is the code
>> >
>> > Private Sub Storage_Click()
>> >
>> >    On Error GoTo Err_Storage_Click
>> >
>> >    Dim ExcelApp As Object
>> >    Dim ExcelWasNotRunning As Boolean           ' Flag for final release
>> >    Dim XLFilePath As String
>> >    Dim XLName As String                        ' Excel file name from
>> > Paths
>> >    Dim MyDb As Database
>> >    Dim Msg As String
>> >
>> >    ' Find the normal path
>> >                                          ' Folder and File
>> >    If Nz(ExcelPath) = "" Then
>> >        ExcelPath = "C:\Storage.XLS"
>> >    End If
>> >
>> >    XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\"))
> '
>> > File Name
>> >
>> >    If Dir(ExcelPath) <> XLName Then          ' Not found
>> >        XLFilePath = FindFile("C:\Documents and Settings\Phil\My
>> > Documents\Access\MDB\WFYC\", XLName)
>> >        Msg = "The name of the file you have selected is " & vbCrLf
>> >        Msg = Msg & XLFilePath & vbCrLf
>> >        Msg = Msg & "but the original file was " & vbCrLf
>> >        Msg = Msg & ExcelPath & vbCrLf
>> >        Msg = Msg & "Do you want to use the new name in future?"
>> >        If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
>> >            ExcelPath = XLFilePath
>> >        End If
>> >    End If
>> >
>> >    Set ExcelApp = CreateObject("Excel.Application")
>> >
>> >    If Err.Number <> 0 Then ExcelWasNotRunning = True
>> >    Err.Clear    ' Clear Err object in case error occurred.
>> >
>> >    ' Set the object variable to reference the file you want to see.
>> >
>> >    Set ExcelApp = CreateObject("Excel.Application")
>> >    ExcelApp.WorkBooks.Open ExcelPath, , True                   ' Read
> Only
>> >    ExcelApp.Visible = True
>> >
>> > Exit_Storage_Click:
>> >    Exit Sub
>> >
>> > Err_Storage_Click:
>> >    If Err = 2447 Then              ' Corrupted File name (with # sign)
>> >        Resume Next
>> >    Else
>> >        MsgBox Err.Description
>> >        Resume Exit_Storage_Click
>> >    End If
>> >
>> > End Sub
>> >
>> > When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
>> > Failed" and the message "The database has been placed in a state by 
>> > user
>> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or
> locked"
>> >
>> > What am I doing wrong
>> >
>> > Thank
>> >
>> > Phil
>> >
>>
>>
>
> 


0
phil126 (320)
5/25/2008 8:01:25 PM
Hi Phil,

I would just like to ask something so that I can understand this
correctly: Are you having the Access application and the Excel
application open at the same time? That is to say, have you got Excel
and Access running at the same time, then from Excel try to open the
same database with an Access object, and vice versa?

Cheers

The Frog
0
5/26/2008 8:06:46 AM
The intention is to open the Excel application from a button on an Access 
form (and leave Access running)

If I open access then open the Excel file by clicking "Storage.XLS" or 
opening Excel and selecting the Storage file absolutely no problem. It 
doesn't matter whether I open Excel or Access first
There is no problem having both open at the same time.
As you surmise the Excel application uses a query within that Access 
application to load it's data.

The only problem is trying to open the Excel file from the command button on 
an Access form (codes posted)

Thanks

Phil

"The Frog" <Mr.Frog.to.you@googlemail.com> wrote in message 
news:4ef45a26-3b9b-40f0-9d71-d650beb681ac@26g2000hsk.googlegroups.com...
> Hi Phil,
>
> I would just like to ask something so that I can understand this
> correctly: Are you having the Access application and the Excel
> application open at the same time? That is to say, have you got Excel
> and Access running at the same time, then from Excel try to open the
> same database with an Access object, and vice versa?
>
> Cheers
>
> The Frog 


0
phil126 (320)
5/26/2008 8:36:55 AM
On May 25, 2:01=A0pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Thanks for coming back
>
> Code is:-
> Function GetAccess()
> '
> ' Macro1 Macro
> ' Macro recorded 26/03/2008 by Phil Stanton
> '
> =A0 =A0 Dim MDBName As String, DefaultDirectory As String, SQLStg As Strin=
g
>
> =A0 =A0 On Error GoTo GetAccess_Err
>
> =A0 =A0 Worksheets("Linked Data").Activate
> =A0 =A0 With ActiveSheet
> =A0 =A0 =A0 =A0 MDBName =3D .Range("A1")
>
> CheckFile:
> =A0 =A0 =A0 =A0 If Dir(MDBName) =3D "" Then =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0' Not found
> =A0 =A0 =A0 =A0 =A0 =A0 MDBName =3D Application.GetOpenFileName("Access Da=
tabase ,*.mde",
> , "Where is the Club Database?")
> =A0 =A0 =A0 =A0 =A0 =A0 If MsgBox("Do you want to use this database in fut=
ure?",
> vbQuestion + vbYesNo) =3D vbYes Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Range("A1") =3D MDBName
> =A0 =A0 =A0 =A0 =A0 =A0 End If
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 End With
>
> '
> =A0 =A0 ' Clear Cells
>
> =A0 =A0 ActiveSheet.Range("A2:H300").Clear
>
> =A0 =A0 SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos=
, YPos,
> XLabelPosition, YLabelPosition, LabelAngle "
> =A0 =A0 SQLStg =3D SQLStg & "FROM `" & MDBName & "`." =A0 =A0 =A0 =A0 ' No=
te the ` symbol.
> Significant
>
> =A0 =A0 DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> =A0 =A0 'DBName =3D "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"=

> =A0 =A0 =A0 =A0 =A0 =A0 '"ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName =
&
> ";DefaultDir=3DC:\WFYC Data\Phil's Folder\Acces"
> =A0 =A0 With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array( _
> =A0 =A0 =A0 =A0 "ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName & ";Defau=
ltDir=3D" &
> DefaultDirectory _
> =A0 =A0 =A0 =A0 ), Array("s\MDB;DriverId=3D25;FIL=3DMS
> Access;MaxBufferSize=3D2048;PageTimeout=3D5;")), _
> =A0 =A0 =A0 =A0 Destination:=3DRange("A2"))
> =A0 =A0 =A0 =A0 .CommandText =3D Array(SQLStg, "QSpaceAllocation QSpaceAll=
ocation
> ORDER BY TypeOfSpace, Space")
> =A0 =A0 =A0 =A0 .Name =3D "Query from MS Access Database"
> =A0 =A0 =A0 =A0 .FieldNames =3D True
> =A0 =A0 =A0 =A0 .RowNumbers =3D False
> =A0 =A0 =A0 =A0 .FillAdjacentFormulas =3D False
> =A0 =A0 =A0 =A0 .PreserveFormatting =3D True
> =A0 =A0 =A0 =A0 .RefreshOnFileOpen =3D False
> =A0 =A0 =A0 =A0 .BackgroundQuery =3D True
> =A0 =A0 =A0 =A0 .RefreshStyle =3D xlOverwriteCells
> =A0 =A0 =A0 =A0 .SavePassword =3D True
> =A0 =A0 =A0 =A0 .SaveData =3D True
> =A0 =A0 =A0 =A0 .AdjustColumnWidth =3D True
> =A0 =A0 =A0 =A0 .RefreshPeriod =3D 0
> =A0 =A0 =A0 =A0 .PreserveColumnInfo =3D True
> =A0 =A0 =A0 =A0 .Refresh BackgroundQuery:=3DTrue
> =A0 =A0 End With
>
> =A0 =A0 'Stop =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0' Has it got here?
>
> =A0 =A0 Exit Function
>
> GetAccess_Err:
> Stop
> =A0 =A0 If Err =3D 1234 Then =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0' No Idea what the error
> code will be
> =A0 =A0 =A0 =A0 ThisWorkbook.Close , False
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 MsgBox Err.Description
> =A0 =A0 End If
>
> End Function
>
> There is a lot of other code, but this is the bit where the data is
> "extracted" from Access.
> As I said, no problem whether the Access DB is open or not, the problem
> comes when I click the button on Access form to show the Excel file.
> The "Linked Data" worksheet holds and processes the data, and the output i=
s
> 11 labelled scatter charts, one on each page (Map of boat compound and whe=
re
> each boat is stored)
>
> Thanks again
>
> Phil
>
> "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
>
>
> > Please post the code that is in the Excel file. =A0If that code refers t=
o
> > the
> > database, then that is most likely where the error is occuring, in which=

> > case, we might be better able to help you.
>
> > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> >> Sorry
>
> >> Should have added
>
> >> There is some code in the Excel application which refers to the Databas=
e
> > and
> >> extracts the relevant information
>
> >> No problem opening Excel whether the database is open or not.
>
> >> Phil
>
> >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> >> >I have a form with a button which is supposed to open an Excel file
> >> >(With
> >> >lots of Macros /VBA) in it. The Excel file gets it's data from the
> >> >Access
> >> >program
>
> >> > Here is the code
>
> >> > Private Sub Storage_Click()
>
> >> > =A0 =A0On Error GoTo Err_Storage_Click
>
> >> > =A0 =A0Dim ExcelApp As Object
> >> > =A0 =A0Dim ExcelWasNotRunning As Boolean =A0 =A0 =A0 =A0 =A0 ' Flag f=
or final release
> >> > =A0 =A0Dim XLFilePath As String
> >> > =A0 =A0Dim XLName As String =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0' Excel file name from
> >> > Paths
> >> > =A0 =A0Dim MyDb As Database
> >> > =A0 =A0Dim Msg As String
>
> >> > =A0 =A0' Find the normal path
> >> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0' Folder and File
> >> > =A0 =A0If Nz(ExcelPath) =3D "" Then
> >> > =A0 =A0 =A0 =A0ExcelPath =3D "C:\Storage.XLS"
> >> > =A0 =A0End If
>
> >> > =A0 =A0XLName =3D Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPat=
h, "\"))
> > '
> >> > File Name
>
> >> > =A0 =A0If Dir(ExcelPath) <> XLName Then =A0 =A0 =A0 =A0 =A0' Not foun=
d
> >> > =A0 =A0 =A0 =A0XLFilePath =3D FindFile("C:\Documents and Settings\Phi=
l\My
> >> > Documents\Access\MDB\WFYC\", XLName)
> >> > =A0 =A0 =A0 =A0Msg =3D "The name of the file you have selected is " &=
 vbCrLf
> >> > =A0 =A0 =A0 =A0Msg =3D Msg & XLFilePath & vbCrLf
> >> > =A0 =A0 =A0 =A0Msg =3D Msg & "but the original file was " & vbCrLf
> >> > =A0 =A0 =A0 =A0Msg =3D Msg & ExcelPath & vbCrLf
> >> > =A0 =A0 =A0 =A0Msg =3D Msg & "Do you want to use the new name in futu=
re?"
> >> > =A0 =A0 =A0 =A0If MsgBox(Msg, vbQuestion + vbYesNo) =3D vbYes Then
> >> > =A0 =A0 =A0 =A0 =A0 =A0ExcelPath =3D XLFilePath
> >> > =A0 =A0 =A0 =A0End If
> >> > =A0 =A0End If
>
> >> > =A0 =A0Set ExcelApp =3D CreateObject("Excel.Application")
>
> >> > =A0 =A0If Err.Number <> 0 Then ExcelWasNotRunning =3D True
> >> > =A0 =A0Err.Clear =A0 =A0' Clear Err object in case error occurred.
>
> >> > =A0 =A0' Set the object variable to reference the file you want to se=
e.
>
> >> > =A0 =A0Set ExcelApp =3D CreateObject("Excel.Application")
> >> > =A0 =A0ExcelApp.WorkBooks.Open ExcelPath, , True =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 ' Read
> > Only
> >> > =A0 =A0ExcelApp.Visible =3D True
>
> >> > Exit_Storage_Click:
> >> > =A0 =A0Exit Sub
>
> >> > Err_Storage_Click:
> >> > =A0 =A0If Err =3D 2447 Then =A0 =A0 =A0 =A0 =A0 =A0 =A0' Corrupted Fi=
le name (with # sign)
> >> > =A0 =A0 =A0 =A0Resume Next
> >> > =A0 =A0Else
> >> > =A0 =A0 =A0 =A0MsgBox Err.Description
> >> > =A0 =A0 =A0 =A0Resume Exit_Storage_Click
> >> > =A0 =A0End If
>
> >> > End Sub
>
> >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver Login=

> >> > Failed" and the message "The database has been placed in a state by
> >> > user
> >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or
> > locked"
>
> >> > What am I doing wrong
>
> >> > Thank
>
> >> > Phil- Hide quoted text -
>
> - Show quoted text -

what's the name of the table being selected in the mdbname ?
    SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
YPos,
XLabelPosition, YLabelPosition, LabelAngle "
    SQLStg =3D SQLStg & "FROM `" & MDBName & "`."         ' Note the `
symbol.
Significant

0
lesperancer (750)
5/26/2008 10:29:45 AM
Hi Roger,

Thought you might be on to something, so rewrote the GetAccess() module 
which still works. Bit more readable as all the examples of the arrays in 
the AddConnection line were a foul mess.

Still same problem though

Phil

Function GetAccess()
'
' Macro1 Macro
' Macro recorded 26/03/2008 by Phil Stanton
'
    Dim MDBName As String, DefaultDirectory As String, SQLStg As String

    On Error GoTo GetAccess_Err

    Worksheets("Linked Data").Activate
    With ActiveSheet
        MDBName = .Range("A1")

CheckFile:
        If Dir(MDBName) = "" Then                  ' Not found
            MDBName = Application.GetOpenFileName("Access Database ,*.mde", 
, "Where is the Club Database?")
            If MsgBox("Do you want to use this database in future?", 
vbQuestion + vbYesNo) = vbYes Then
                .Range("A1") = MDBName
            End If
        End If
    End With

'
    ' Clear Cells

    ActiveSheet.Range("A2:H300").ClearContents

    SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YPos, 
XLabelPosition, YLabelPosition, LabelAngle "
    SQLStg = SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, Space"

    DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS 
Access Database;"), _
        Array("DBQ=" & MDBName & ";"), _
        Array("DefaultDir=" & DefaultDirectory & ";DriverId=25;"), _
        Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        )), Destination:=Range("A2"))
        .CommandText = Array(SQLStg)
        .Name = "Query from MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=True
    End With

    Exit Function

GetAccess_Err:
Stop
    If Err = 12 Then
        ThisWorkbook.Close , False
    Else
        MsgBox Err.Description
    End If

End Function
"Roger" <lesperancer@natpro.com> wrote in message 
news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.com...
On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Thanks for coming back
>
> Code is:-
> Function GetAccess()
> '
> ' Macro1 Macro
> ' Macro recorded 26/03/2008 by Phil Stanton
> '
> Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> On Error GoTo GetAccess_Err
>
> Worksheets("Linked Data").Activate
> With ActiveSheet
> MDBName = .Range("A1")
>
> CheckFile:
> If Dir(MDBName) = "" Then ' Not found
> MDBName = Application.GetOpenFileName("Access Database ,*.mde",
> , "Where is the Club Database?")
> If MsgBox("Do you want to use this database in future?",
> vbQuestion + vbYesNo) = vbYes Then
> .Range("A1") = MDBName
> End If
> End If
> End With
>
> '
> ' Clear Cells
>
> ActiveSheet.Range("A2:H300").Clear
>
> SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YPos,
> XLabelPosition, YLabelPosition, LabelAngle "
> SQLStg = SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> Significant
>
> DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))
>
> 'DBName = "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
> '"ODBC;DSN=MS Access Database;DBQ=" & MDBName &
> ";DefaultDir=C:\WFYC Data\Phil's Folder\Acces"
> With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
> "ODBC;DSN=MS Access Database;DBQ=" & MDBName & ";DefaultDir=" &
> DefaultDirectory _
> ), Array("s\MDB;DriverId=25;FIL=MS
> Access;MaxBufferSize=2048;PageTimeout=5;")), _
> Destination:=Range("A2"))
> .CommandText = Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> ORDER BY TypeOfSpace, Space")
> .Name = "Query from MS Access Database"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .BackgroundQuery = True
> .RefreshStyle = xlOverwriteCells
> .SavePassword = True
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .PreserveColumnInfo = True
> .Refresh BackgroundQuery:=True
> End With
>
> 'Stop ' Has it got here?
>
> Exit Function
>
> GetAccess_Err:
> Stop
> If Err = 1234 Then ' No Idea what the error
> code will be
> ThisWorkbook.Close , False
> Else
> MsgBox Err.Description
> End If
>
> End Function
>
> There is a lot of other code, but this is the bit where the data is
> "extracted" from Access.
> As I said, no problem whether the Access DB is open or not, the problem
> comes when I click the button on Access form to show the Excel file.
> The "Linked Data" worksheet holds and processes the data, and the output 
> is
> 11 labelled scatter charts, one on each page (Map of boat compound and 
> where
> each boat is stored)
>
> Thanks again
>
> Phil
>
> "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
>
>
> > Please post the code that is in the Excel file. If that code refers to
> > the
> > database, then that is most likely where the error is occuring, in which
> > case, we might be better able to help you.
>
> > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> >> Sorry
>
> >> Should have added
>
> >> There is some code in the Excel application which refers to the 
> >> Database
> > and
> >> extracts the relevant information
>
> >> No problem opening Excel whether the database is open or not.
>
> >> Phil
>
> >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> >> >I have a form with a button which is supposed to open an Excel file
> >> >(With
> >> >lots of Macros /VBA) in it. The Excel file gets it's data from the
> >> >Access
> >> >program
>
> >> > Here is the code
>
> >> > Private Sub Storage_Click()
>
> >> > On Error GoTo Err_Storage_Click
>
> >> > Dim ExcelApp As Object
> >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> >> > Dim XLFilePath As String
> >> > Dim XLName As String ' Excel file name from
> >> > Paths
> >> > Dim MyDb As Database
> >> > Dim Msg As String
>
> >> > ' Find the normal path
> >> > ' Folder and File
> >> > If Nz(ExcelPath) = "" Then
> >> > ExcelPath = "C:\Storage.XLS"
> >> > End If
>
> >> > XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\"))
> > '
> >> > File Name
>
> >> > If Dir(ExcelPath) <> XLName Then ' Not found
> >> > XLFilePath = FindFile("C:\Documents and Settings\Phil\My
> >> > Documents\Access\MDB\WFYC\", XLName)
> >> > Msg = "The name of the file you have selected is " & vbCrLf
> >> > Msg = Msg & XLFilePath & vbCrLf
> >> > Msg = Msg & "but the original file was " & vbCrLf
> >> > Msg = Msg & ExcelPath & vbCrLf
> >> > Msg = Msg & "Do you want to use the new name in future?"
> >> > If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
> >> > ExcelPath = XLFilePath
> >> > End If
> >> > End If
>
> >> > Set ExcelApp = CreateObject("Excel.Application")
>
> >> > If Err.Number <> 0 Then ExcelWasNotRunning = True
> >> > Err.Clear ' Clear Err object in case error occurred.
>
> >> > ' Set the object variable to reference the file you want to see.
>
> >> > Set ExcelApp = CreateObject("Excel.Application")
> >> > ExcelApp.WorkBooks.Open ExcelPath, , True ' Read
> > Only
> >> > ExcelApp.Visible = True
>
> >> > Exit_Storage_Click:
> >> > Exit Sub
>
> >> > Err_Storage_Click:
> >> > If Err = 2447 Then ' Corrupted File name (with # sign)
> >> > Resume Next
> >> > Else
> >> > MsgBox Err.Description
> >> > Resume Exit_Storage_Click
> >> > End If
>
> >> > End Sub
>
> >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
> >> > Failed" and the message "The database has been placed in a state by
> >> > user
> >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or
> > locked"
>
> >> > What am I doing wrong
>
> >> > Thank
>
> >> > Phil- Hide quoted text -
>
> - Show quoted text -

what's the name of the table being selected in the mdbname ?
    SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
YPos,
XLabelPosition, YLabelPosition, LabelAngle "
    SQLStg = SQLStg & "FROM `" & MDBName & "`."         ' Note the `
symbol.
Significant


0
phil126 (320)
5/26/2008 9:33:00 PM
Hi Phil,

I know what the issue is here. Basically it works like this:

1/ You open an Access MDB in MS Access. This MDB has both the data and
the forms in it.

2/ On a form in MS Access you click a button and launch Excel.

3/ When Excel is launched the macro code you have posted tries to run
and returns with an error.

If this sequence of events is correct then i know why.

The reason is that the MDB file is in exclusive mode, and at the same
time you are trying to access it from another application. In short
you are trying to create an object / read from something that is
already in use / exists.

How to solve this.....?????

How I would approach this is as follows:

1/ Create an ADO recordset to hold the query results and disconnect
it.
2/ Create an Excel Application Object in Access (not try the other
way)
3/ Control the Excel object as you see fit to display the results from
the ADO recordset. (something like looping through the recordset and
doing a worksheets("SheetName").cells(row,column).value =
ADORecordsetObjectName.Fields("Fieldname").value , or something like
that. Dont forget to increment your row numbers in Excel!).
4/ Destroy the recordset
5/ If you want to show excel, then control this from the Access
Application, or alternatively save the Excel workbook you are working
on (do this by controlling the object) and then use a second module /
macro in Access to launch Excel and load the workbook with the fresh
results.

I hope this helps

Cheers

The Frog
0
5/27/2008 7:35:04 AM
Hi

You are absolutely right in the sequence of events.

Where I am not so sure is that once both Access ans Excel are opened (the 
latter by launching Excel directly) I can change data in Access, then press 
a button on the Excel sheet to refresh the data with no trouble. This button 
runs the same GetAccess() routine.

Where do you suggest I create the ADO recordset, within Access or Excel?

Thanks

Phil


"The Frog" <Mr.Frog.to.you@googlemail.com> wrote in message 
news:71d5dc28-2b38-42ed-9249-0f81d524f618@r66g2000hsg.googlegroups.com...
> Hi Phil,
>
> I know what the issue is here. Basically it works like this:
>
> 1/ You open an Access MDB in MS Access. This MDB has both the data and
> the forms in it.
>
> 2/ On a form in MS Access you click a button and launch Excel.
>
> 3/ When Excel is launched the macro code you have posted tries to run
> and returns with an error.
>
> If this sequence of events is correct then i know why.
>
> The reason is that the MDB file is in exclusive mode, and at the same
> time you are trying to access it from another application. In short
> you are trying to create an object / read from something that is
> already in use / exists.
>
> How to solve this.....?????
>
> How I would approach this is as follows:
>
> 1/ Create an ADO recordset to hold the query results and disconnect
> it.
> 2/ Create an Excel Application Object in Access (not try the other
> way)
> 3/ Control the Excel object as you see fit to display the results from
> the ADO recordset. (something like looping through the recordset and
> doing a worksheets("SheetName").cells(row,column).value =
> ADORecordsetObjectName.Fields("Fieldname").value , or something like
> that. Dont forget to increment your row numbers in Excel!).
> 4/ Destroy the recordset
> 5/ If you want to show excel, then control this from the Access
> Application, or alternatively save the Excel workbook you are working
> on (do this by controlling the object) and then use a second module /
> macro in Access to launch Excel and load the workbook with the fresh
> results.
>
> I hope this helps
>
> Cheers
>
> The Frog 


0
phil126 (320)
5/27/2008 10:52:45 AM
On May 26, 3:33=A0pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Hi Roger,
>
> Thought you might be on to something, so rewrote the GetAccess() module
> which still works. Bit more readable as all the examples of the arrays in
> the AddConnection line were a foul mess.
>
> Still same problem though
>
> Phil
>
> Function GetAccess()
> '
> ' Macro1 Macro
> ' Macro recorded 26/03/2008 by Phil Stanton
> '
> =A0 =A0 Dim MDBName As String, DefaultDirectory As String, SQLStg As Strin=
g
>
> =A0 =A0 On Error GoTo GetAccess_Err
>
> =A0 =A0 Worksheets("Linked Data").Activate
> =A0 =A0 With ActiveSheet
> =A0 =A0 =A0 =A0 MDBName =3D .Range("A1")
>
> CheckFile:
> =A0 =A0 =A0 =A0 If Dir(MDBName) =3D "" Then =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0' Not found
> =A0 =A0 =A0 =A0 =A0 =A0 MDBName =3D Application.GetOpenFileName("Access Da=
tabase ,*.mde",
> , "Where is the Club Database?")
> =A0 =A0 =A0 =A0 =A0 =A0 If MsgBox("Do you want to use this database in fut=
ure?",
> vbQuestion + vbYesNo) =3D vbYes Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Range("A1") =3D MDBName
> =A0 =A0 =A0 =A0 =A0 =A0 End If
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 End With
>
> '
> =A0 =A0 ' Clear Cells
>
> =A0 =A0 ActiveSheet.Range("A2:H300").ClearContents
>
> =A0 =A0 SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos=
, YPos,
> XLabelPosition, YLabelPosition, LabelAngle "
> =A0 =A0 SQLStg =3D SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, S=
pace"
>
> =A0 =A0 DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> =A0 =A0 With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array("ODBC;D=
SN=3DMS
> Access Database;"), _
> =A0 =A0 =A0 =A0 Array("DBQ=3D" & MDBName & ";"), _
> =A0 =A0 =A0 =A0 Array("DefaultDir=3D" & DefaultDirectory & ";DriverId=3D25=
;"), _
> =A0 =A0 =A0 =A0 Array("FIL=3DMS Access;MaxBufferSize=3D2048;PageTimeout=3D=
5;" _
> =A0 =A0 =A0 =A0 )), Destination:=3DRange("A2"))
> =A0 =A0 =A0 =A0 .CommandText =3D Array(SQLStg)
> =A0 =A0 =A0 =A0 .Name =3D "Query from MS Access Database"
> =A0 =A0 =A0 =A0 .FieldNames =3D True
> =A0 =A0 =A0 =A0 .RowNumbers =3D False
> =A0 =A0 =A0 =A0 .FillAdjacentFormulas =3D False
> =A0 =A0 =A0 =A0 .PreserveFormatting =3D True
> =A0 =A0 =A0 =A0 .RefreshOnFileOpen =3D False
> =A0 =A0 =A0 =A0 .BackgroundQuery =3D True
> =A0 =A0 =A0 =A0 .RefreshStyle =3D xlOverwriteCells
> =A0 =A0 =A0 =A0 .SavePassword =3D True
> =A0 =A0 =A0 =A0 .SaveData =3D True
> =A0 =A0 =A0 =A0 .AdjustColumnWidth =3D True
> =A0 =A0 =A0 =A0 .RefreshPeriod =3D 0
> =A0 =A0 =A0 =A0 .PreserveColumnInfo =3D True
> =A0 =A0 =A0 =A0 .Refresh BackgroundQuery:=3DTrue
> =A0 =A0 End With
>
> =A0 =A0 Exit Function
>
> GetAccess_Err:
> Stop
> =A0 =A0 If Err =3D 12 Then
> =A0 =A0 =A0 =A0 ThisWorkbook.Close , False
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 MsgBox Err.Description
> =A0 =A0 End If
>
> End Function"Roger" <lesperan...@natpro.com> wrote in message
>
> news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.com...
> On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
>
>
>
>
> > Thanks for coming back
>
> > Code is:-
> > Function GetAccess()
> > '
> > ' Macro1 Macro
> > ' Macro recorded 26/03/2008 by Phil Stanton
> > '
> > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > On Error GoTo GetAccess_Err
>
> > Worksheets("Linked Data").Activate
> > With ActiveSheet
> > MDBName =3D .Range("A1")
>
> > CheckFile:
> > If Dir(MDBName) =3D "" Then ' Not found
> > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > , "Where is the Club Database?")
> > If MsgBox("Do you want to use this database in future?",
> > vbQuestion + vbYesNo) =3D vbYes Then
> > .Range("A1") =3D MDBName
> > End If
> > End If
> > End With
>
> > '
> > ' Clear Cells
>
> > ActiveSheet.Range("A2:H300").Clear
>
> > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YPos=
,
> > XLabelPosition, YLabelPosition, LabelAngle "
> > SQLStg =3D SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> > Significant
>
> > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > 'DBName =3D "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
> > '"ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName &
> > ";DefaultDir=3DC:\WFYC Data\Phil's Folder\Acces"
> > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array( _
> > "ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName & ";DefaultDir=3D" &
> > DefaultDirectory _
> > ), Array("s\MDB;DriverId=3D25;FIL=3DMS
> > Access;MaxBufferSize=3D2048;PageTimeout=3D5;")), _
> > Destination:=3DRange("A2"))
> > .CommandText =3D Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > ORDER BY TypeOfSpace, Space")
> > .Name =3D "Query from MS Access Database"
> > .FieldNames =3D True
> > .RowNumbers =3D False
> > .FillAdjacentFormulas =3D False
> > .PreserveFormatting =3D True
> > .RefreshOnFileOpen =3D False
> > .BackgroundQuery =3D True
> > .RefreshStyle =3D xlOverwriteCells
> > .SavePassword =3D True
> > .SaveData =3D True
> > .AdjustColumnWidth =3D True
> > .RefreshPeriod =3D 0
> > .PreserveColumnInfo =3D True
> > .Refresh BackgroundQuery:=3DTrue
> > End With
>
> > 'Stop ' Has it got here?
>
> > Exit Function
>
> > GetAccess_Err:
> > Stop
> > If Err =3D 1234 Then ' No Idea what the error
> > code will be
> > ThisWorkbook.Close , False
> > Else
> > MsgBox Err.Description
> > End If
>
> > End Function
>
> > There is a lot of other code, but this is the bit where the data is
> > "extracted" from Access.
> > As I said, no problem whether the Access DB is open or not, the problem
> > comes when I click the button on Access form to show the Excel file.
> > The "Linked Data" worksheet holds and processes the data, and the output=

> > is
> > 11 labelled scatter charts, one on each page (Map of boat compound and
> > where
> > each boat is stored)
>
> > Thanks again
>
> > Phil
>
> > "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> >news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
> > > Please post the code that is in the Excel file. If that code refers to=

> > > the
> > > database, then that is most likely where the error is occuring, in whi=
ch
> > > case, we might be better able to help you.
>
> > > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> > >> Sorry
>
> > >> Should have added
>
> > >> There is some code in the Excel application which refers to the
> > >> Database
> > > and
> > >> extracts the relevant information
>
> > >> No problem opening Excel whether the database is open or not.
>
> > >> Phil
>
> > >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> > >> >I have a form with a button which is supposed to open an Excel file
> > >> >(With
> > >> >lots of Macros /VBA) in it. The Excel file gets it's data from the
> > >> >Access
> > >> >program
>
> > >> > Here is the code
>
> > >> > Private Sub Storage_Click()
>
> > >> > On Error GoTo Err_Storage_Click
>
> > >> > Dim ExcelApp As Object
> > >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> > >> > Dim XLFilePath As String
> > >> > Dim XLName As String ' Excel file name from
> > >> > Paths
> > >> > Dim MyDb As Database
> > >> > Dim Msg As String
>
> > >> > ' Find the normal path
> > >> > ' Folder and File
> > >> > If Nz(ExcelPath) =3D "" Then
> > >> > ExcelPath =3D "C:\Storage.XLS"
> > >> > End If
>
> > >> > XLName =3D Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\=
"))
> > > '
> > >> > File Name
>
> > >> > If Dir(ExcelPath) <> XLName Then ' Not found
> > >> > XLFilePath =3D FindFile("C:\Documents and Settings\Phil\My
> > >> > Documents\Access\MDB\WFYC\", XLName)
> > >> > Msg =3D "The name of the file you have selected is " & vbCrLf
> > >> > Msg =3D Msg & XLFilePath & vbCrLf
> > >> > Msg =3D Msg & "but the original file was " & vbCrLf
> > >> > Msg =3D Msg & ExcelPath & vbCrLf
> > >> > Msg =3D Msg & "Do you want to use the new name in future?"
> > >> > If MsgBox(Msg, vbQuestion + vbYesNo) =3D vbYes Then
> > >> > ExcelPath =3D XLFilePath
> > >> > End If
> > >> > End If
>
> > >> > Set ExcelApp =3D CreateObject("Excel.Application")
>
> > >> > If Err.Number <> 0 Then ExcelWasNotRunning =3D True
> > >> > Err.Clear ' Clear Err object in case error occurred.
>
> > >> > ' Set the object variable to reference the file you want to see.
>
> > >> > Set ExcelApp =3D CreateObject("Excel.Application")
> > >> > ExcelApp.WorkBooks.Open ExcelPath, , True ' Read
> > > Only
> > >> > ExcelApp.Visible =3D True
>
> > >> > Exit_Storage_Click:
> > >> > Exit Sub
>
> > >> > Err_Storage_Click:
> > >> > If Err =3D 2447 Then ' Corrupted File name (with # sign)
> > >> > Resume Next
> > >> > Else
> > >> > MsgBox Err.Description
> > >> > Resume Exit_Storage_Click
> > >> > End If
>
> > >> > End Sub
>
> > >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver Log=
in
> > >> > Failed" and the message "The database has been placed in a state by=

> > >> > user
> > >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or
> > > locked"
>
> > >> > What am I doing wrong
>
> > >> > Thank
>
> > >> > Phil- Hide quoted text -
>
> > - Show quoted text -
>
> what's the name of the table being selected in the mdbname ?
> =A0 =A0 SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos=
,
> YPos,
> XLabelPosition, YLabelPosition, LabelAngle "
> =A0 =A0 SQLStg =3D SQLStg & "FROM `" & MDBName & "`." =A0 =A0 =A0 =A0 ' No=
te the `
> symbol.
> Significant- Hide quoted text -
>
> - Show quoted text -

ok.. using access97, I've got a test mdb with a table called tblMonth
I created an xls with the function you have above, except I changed
this
  > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName,
  XPos, YPos,
  > > XLabelPosition, YLabelPosition, LabelAngle "
  > > SQLStg =3D SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
  > > Significant

to sqlstg =3D "select * from tblMonth"

and this
  > > .CommandText =3D Array(SQLStg, "QSpaceAllocation QSpaceAllocation
  > > ORDER BY TypeOfSpace, Space")

to .commandtext =3D array(sqlstg)

and stepped through the function and got the external data I expected

I saved and closed excel, opened my mdb, made of change to a record
leaving the mdb open, I opened excel, hit refresh, and got the changed
record's data

closed excel, changed another record, opened the form, click the
button that has your code,
it opened excel no problem and refreshing the data showed the correct
data

I wonder if this is because you're using an MDE ?
0
lesperancer (750)
5/27/2008 10:52:52 AM
Hi Rger

Tried changing it to an MDB but no luck. It's a back end front end DB, so 
the Front end, wher it is an Mdb or Mde is still looking at the same tables

Thanks


"Roger" <lesperancer@natpro.com> wrote in message 
news:83ba424f-d177-4609-95d3-fca008d5963f@y38g2000hsy.googlegroups.com...
On May 26, 3:33 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Hi Roger,
>
> Thought you might be on to something, so rewrote the GetAccess() module
> which still works. Bit more readable as all the examples of the arrays in
> the AddConnection line were a foul mess.
>
> Still same problem though
>
> Phil
>
> Function GetAccess()
> '
> ' Macro1 Macro
> ' Macro recorded 26/03/2008 by Phil Stanton
> '
> Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> On Error GoTo GetAccess_Err
>
> Worksheets("Linked Data").Activate
> With ActiveSheet
> MDBName = .Range("A1")
>
> CheckFile:
> If Dir(MDBName) = "" Then ' Not found
> MDBName = Application.GetOpenFileName("Access Database ,*.mde",
> , "Where is the Club Database?")
> If MsgBox("Do you want to use this database in future?",
> vbQuestion + vbYesNo) = vbYes Then
> .Range("A1") = MDBName
> End If
> End If
> End With
>
> '
> ' Clear Cells
>
> ActiveSheet.Range("A2:H300").ClearContents
>
> SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YPos,
> XLabelPosition, YLabelPosition, LabelAngle "
> SQLStg = SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, Space"
>
> DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))
>
> With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS
> Access Database;"), _
> Array("DBQ=" & MDBName & ";"), _
> Array("DefaultDir=" & DefaultDirectory & ";DriverId=25;"), _
> Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
> )), Destination:=Range("A2"))
> .CommandText = Array(SQLStg)
> .Name = "Query from MS Access Database"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .BackgroundQuery = True
> .RefreshStyle = xlOverwriteCells
> .SavePassword = True
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .PreserveColumnInfo = True
> .Refresh BackgroundQuery:=True
> End With
>
> Exit Function
>
> GetAccess_Err:
> Stop
> If Err = 12 Then
> ThisWorkbook.Close , False
> Else
> MsgBox Err.Description
> End If
>
> End Function"Roger" <lesperan...@natpro.com> wrote in message
>
> news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.com...
> On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
>
>
>
>
> > Thanks for coming back
>
> > Code is:-
> > Function GetAccess()
> > '
> > ' Macro1 Macro
> > ' Macro recorded 26/03/2008 by Phil Stanton
> > '
> > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > On Error GoTo GetAccess_Err
>
> > Worksheets("Linked Data").Activate
> > With ActiveSheet
> > MDBName = .Range("A1")
>
> > CheckFile:
> > If Dir(MDBName) = "" Then ' Not found
> > MDBName = Application.GetOpenFileName("Access Database ,*.mde",
> > , "Where is the Club Database?")
> > If MsgBox("Do you want to use this database in future?",
> > vbQuestion + vbYesNo) = vbYes Then
> > .Range("A1") = MDBName
> > End If
> > End If
> > End With
>
> > '
> > ' Clear Cells
>
> > ActiveSheet.Range("A2:H300").Clear
>
> > SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YPos,
> > XLabelPosition, YLabelPosition, LabelAngle "
> > SQLStg = SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> > Significant
>
> > DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))
>
> > 'DBName = "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
> > '"ODBC;DSN=MS Access Database;DBQ=" & MDBName &
> > ";DefaultDir=C:\WFYC Data\Phil's Folder\Acces"
> > With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
> > "ODBC;DSN=MS Access Database;DBQ=" & MDBName & ";DefaultDir=" &
> > DefaultDirectory _
> > ), Array("s\MDB;DriverId=25;FIL=MS
> > Access;MaxBufferSize=2048;PageTimeout=5;")), _
> > Destination:=Range("A2"))
> > .CommandText = Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > ORDER BY TypeOfSpace, Space")
> > .Name = "Query from MS Access Database"
> > .FieldNames = True
> > .RowNumbers = False
> > .FillAdjacentFormulas = False
> > .PreserveFormatting = True
> > .RefreshOnFileOpen = False
> > .BackgroundQuery = True
> > .RefreshStyle = xlOverwriteCells
> > .SavePassword = True
> > .SaveData = True
> > .AdjustColumnWidth = True
> > .RefreshPeriod = 0
> > .PreserveColumnInfo = True
> > .Refresh BackgroundQuery:=True
> > End With
>
> > 'Stop ' Has it got here?
>
> > Exit Function
>
> > GetAccess_Err:
> > Stop
> > If Err = 1234 Then ' No Idea what the error
> > code will be
> > ThisWorkbook.Close , False
> > Else
> > MsgBox Err.Description
> > End If
>
> > End Function
>
> > There is a lot of other code, but this is the bit where the data is
> > "extracted" from Access.
> > As I said, no problem whether the Access DB is open or not, the problem
> > comes when I click the button on Access form to show the Excel file.
> > The "Linked Data" worksheet holds and processes the data, and the output
> > is
> > 11 labelled scatter charts, one on each page (Map of boat compound and
> > where
> > each boat is stored)
>
> > Thanks again
>
> > Phil
>
> > "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> >news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
> > > Please post the code that is in the Excel file. If that code refers to
> > > the
> > > database, then that is most likely where the error is occuring, in 
> > > which
> > > case, we might be better able to help you.
>
> > > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> > >> Sorry
>
> > >> Should have added
>
> > >> There is some code in the Excel application which refers to the
> > >> Database
> > > and
> > >> extracts the relevant information
>
> > >> No problem opening Excel whether the database is open or not.
>
> > >> Phil
>
> > >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> > >> >I have a form with a button which is supposed to open an Excel file
> > >> >(With
> > >> >lots of Macros /VBA) in it. The Excel file gets it's data from the
> > >> >Access
> > >> >program
>
> > >> > Here is the code
>
> > >> > Private Sub Storage_Click()
>
> > >> > On Error GoTo Err_Storage_Click
>
> > >> > Dim ExcelApp As Object
> > >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> > >> > Dim XLFilePath As String
> > >> > Dim XLName As String ' Excel file name from
> > >> > Paths
> > >> > Dim MyDb As Database
> > >> > Dim Msg As String
>
> > >> > ' Find the normal path
> > >> > ' Folder and File
> > >> > If Nz(ExcelPath) = "" Then
> > >> > ExcelPath = "C:\Storage.XLS"
> > >> > End If
>
> > >> > XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, 
> > >> > "\"))
> > > '
> > >> > File Name
>
> > >> > If Dir(ExcelPath) <> XLName Then ' Not found
> > >> > XLFilePath = FindFile("C:\Documents and Settings\Phil\My
> > >> > Documents\Access\MDB\WFYC\", XLName)
> > >> > Msg = "The name of the file you have selected is " & vbCrLf
> > >> > Msg = Msg & XLFilePath & vbCrLf
> > >> > Msg = Msg & "but the original file was " & vbCrLf
> > >> > Msg = Msg & ExcelPath & vbCrLf
> > >> > Msg = Msg & "Do you want to use the new name in future?"
> > >> > If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
> > >> > ExcelPath = XLFilePath
> > >> > End If
> > >> > End If
>
> > >> > Set ExcelApp = CreateObject("Excel.Application")
>
> > >> > If Err.Number <> 0 Then ExcelWasNotRunning = True
> > >> > Err.Clear ' Clear Err object in case error occurred.
>
> > >> > ' Set the object variable to reference the file you want to see.
>
> > >> > Set ExcelApp = CreateObject("Excel.Application")
> > >> > ExcelApp.WorkBooks.Open ExcelPath, , True ' Read
> > > Only
> > >> > ExcelApp.Visible = True
>
> > >> > Exit_Storage_Click:
> > >> > Exit Sub
>
> > >> > Err_Storage_Click:
> > >> > If Err = 2447 Then ' Corrupted File name (with # sign)
> > >> > Resume Next
> > >> > Else
> > >> > MsgBox Err.Description
> > >> > Resume Exit_Storage_Click
> > >> > End If
>
> > >> > End Sub
>
> > >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver 
> > >> > Login
> > >> > Failed" and the message "The database has been placed in a state by
> > >> > user
> > >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or
> > > locked"
>
> > >> > What am I doing wrong
>
> > >> > Thank
>
> > >> > Phil- Hide quoted text -
>
> > - Show quoted text -
>
> what's the name of the table being selected in the mdbname ?
> SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
> YPos,
> XLabelPosition, YLabelPosition, LabelAngle "
> SQLStg = SQLStg & "FROM `" & MDBName & "`." ' Note the `
> symbol.
> Significant- Hide quoted text -
>
> - Show quoted text -

ok.. using access97, I've got a test mdb with a table called tblMonth
I created an xls with the function you have above, except I changed
this
  > > SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName,
  XPos, YPos,
  > > XLabelPosition, YLabelPosition, LabelAngle "
  > > SQLStg = SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
  > > Significant

to sqlstg = "select * from tblMonth"

and this
  > > .CommandText = Array(SQLStg, "QSpaceAllocation QSpaceAllocation
  > > ORDER BY TypeOfSpace, Space")

to .commandtext = array(sqlstg)

and stepped through the function and got the external data I expected

I saved and closed excel, opened my mdb, made of change to a record
leaving the mdb open, I opened excel, hit refresh, and got the changed
record's data

closed excel, changed another record, opened the form, click the
button that has your code,
it opened excel no problem and refreshing the data showed the correct
data

I wonder if this is because you're using an MDE ? 


0
phil126 (320)
5/27/2008 9:09:22 PM
On May 27, 3:09=A0pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Hi Rger
>
> Tried changing it to an MDB but no luck. It's a back end front end DB, so
> the Front end, wher it is an Mdb or Mde is still looking at the same table=
s
>
> Thanks
>
> "Roger" <lesperan...@natpro.com> wrote in message
>
> news:83ba424f-d177-4609-95d3-fca008d5963f@y38g2000hsy.googlegroups.com...
> On May 26, 3:33 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
>
>
>
>
> > Hi Roger,
>
> > Thought you might be on to something, so rewrote the GetAccess() module
> > which still works. Bit more readable as all the examples of the arrays i=
n
> > the AddConnection line were a foul mess.
>
> > Still same problem though
>
> > Phil
>
> > Function GetAccess()
> > '
> > ' Macro1 Macro
> > ' Macro recorded 26/03/2008 by Phil Stanton
> > '
> > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > On Error GoTo GetAccess_Err
>
> > Worksheets("Linked Data").Activate
> > With ActiveSheet
> > MDBName =3D .Range("A1")
>
> > CheckFile:
> > If Dir(MDBName) =3D "" Then ' Not found
> > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > , "Where is the Club Database?")
> > If MsgBox("Do you want to use this database in future?",
> > vbQuestion + vbYesNo) =3D vbYes Then
> > .Range("A1") =3D MDBName
> > End If
> > End If
> > End With
>
> > '
> > ' Clear Cells
>
> > ActiveSheet.Range("A2:H300").ClearContents
>
> > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YPos=
,
> > XLabelPosition, YLabelPosition, LabelAngle "
> > SQLStg =3D SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, Space"
>
> > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array("ODBC;DSN=3DM=
S
> > Access Database;"), _
> > Array("DBQ=3D" & MDBName & ";"), _
> > Array("DefaultDir=3D" & DefaultDirectory & ";DriverId=3D25;"), _
> > Array("FIL=3DMS Access;MaxBufferSize=3D2048;PageTimeout=3D5;" _
> > )), Destination:=3DRange("A2"))
> > .CommandText =3D Array(SQLStg)
> > .Name =3D "Query from MS Access Database"
> > .FieldNames =3D True
> > .RowNumbers =3D False
> > .FillAdjacentFormulas =3D False
> > .PreserveFormatting =3D True
> > .RefreshOnFileOpen =3D False
> > .BackgroundQuery =3D True
> > .RefreshStyle =3D xlOverwriteCells
> > .SavePassword =3D True
> > .SaveData =3D True
> > .AdjustColumnWidth =3D True
> > .RefreshPeriod =3D 0
> > .PreserveColumnInfo =3D True
> > .Refresh BackgroundQuery:=3DTrue
> > End With
>
> > Exit Function
>
> > GetAccess_Err:
> > Stop
> > If Err =3D 12 Then
> > ThisWorkbook.Close , False
> > Else
> > MsgBox Err.Description
> > End If
>
> > End Function"Roger" <lesperan...@natpro.com> wrote in message
>
> >news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.com...=

> > On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > Thanks for coming back
>
> > > Code is:-
> > > Function GetAccess()
> > > '
> > > ' Macro1 Macro
> > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > '
> > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > On Error GoTo GetAccess_Err
>
> > > Worksheets("Linked Data").Activate
> > > With ActiveSheet
> > > MDBName =3D .Range("A1")
>
> > > CheckFile:
> > > If Dir(MDBName) =3D "" Then ' Not found
> > > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > > , "Where is the Club Database?")
> > > If MsgBox("Do you want to use this database in future?",
> > > vbQuestion + vbYesNo) =3D vbYes Then
> > > .Range("A1") =3D MDBName
> > > End If
> > > End If
> > > End With
>
> > > '
> > > ' Clear Cells
>
> > > ActiveSheet.Range("A2:H300").Clear
>
> > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YP=
os,
> > > XLabelPosition, YLabelPosition, LabelAngle "
> > > SQLStg =3D SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> > > Significant
>
> > > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > > 'DBName =3D "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
> > > '"ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName &
> > > ";DefaultDir=3DC:\WFYC Data\Phil's Folder\Acces"
> > > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array( _
> > > "ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName & ";DefaultDir=3D" &
> > > DefaultDirectory _
> > > ), Array("s\MDB;DriverId=3D25;FIL=3DMS
> > > Access;MaxBufferSize=3D2048;PageTimeout=3D5;")), _
> > > Destination:=3DRange("A2"))
> > > .CommandText =3D Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > > ORDER BY TypeOfSpace, Space")
> > > .Name =3D "Query from MS Access Database"
> > > .FieldNames =3D True
> > > .RowNumbers =3D False
> > > .FillAdjacentFormulas =3D False
> > > .PreserveFormatting =3D True
> > > .RefreshOnFileOpen =3D False
> > > .BackgroundQuery =3D True
> > > .RefreshStyle =3D xlOverwriteCells
> > > .SavePassword =3D True
> > > .SaveData =3D True
> > > .AdjustColumnWidth =3D True
> > > .RefreshPeriod =3D 0
> > > .PreserveColumnInfo =3D True
> > > .Refresh BackgroundQuery:=3DTrue
> > > End With
>
> > > 'Stop ' Has it got here?
>
> > > Exit Function
>
> > > GetAccess_Err:
> > > Stop
> > > If Err =3D 1234 Then ' No Idea what the error
> > > code will be
> > > ThisWorkbook.Close , False
> > > Else
> > > MsgBox Err.Description
> > > End If
>
> > > End Function
>
> > > There is a lot of other code, but this is the bit where the data is
> > > "extracted" from Access.
> > > As I said, no problem whether the Access DB is open or not, the proble=
m
> > > comes when I click the button on Access form to show the Excel file.
> > > The "Linked Data" worksheet holds and processes the data, and the outp=
ut
> > > is
> > > 11 labelled scatter charts, one on each page (Map of boat compound and=

> > > where
> > > each boat is stored)
>
> > > Thanks again
>
> > > Phil
>
> > > "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> > >news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
> > > > Please post the code that is in the Excel file. If that code refers =
to
> > > > the
> > > > database, then that is most likely where the error is occuring, in
> > > > which
> > > > case, we might be better able to help you.
>
> > > > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> > > >> Sorry
>
> > > >> Should have added
>
> > > >> There is some code in the Excel application which refers to the
> > > >> Database
> > > > and
> > > >> extracts the relevant information
>
> > > >> No problem opening Excel whether the database is open or not.
>
> > > >> Phil
>
> > > >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> > > >> >I have a form with a button which is supposed to open an Excel fil=
e
> > > >> >(With
> > > >> >lots of Macros /VBA) in it. The Excel file gets it's data from the=

> > > >> >Access
> > > >> >program
>
> > > >> > Here is the code
>
> > > >> > Private Sub Storage_Click()
>
> > > >> > On Error GoTo Err_Storage_Click
>
> > > >> > Dim ExcelApp As Object
> > > >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> > > >> > Dim XLFilePath As String
> > > >> > Dim XLName As String ' Excel file name from
> > > >> > Paths
> > > >> > Dim MyDb As Database
> > > >> > Dim Msg As String
>
> > > >> > ' Find the normal path
> > > >> > ' Folder and File
> > > >> > If Nz(ExcelPath) =3D "" Then
> > > >> > ExcelPath =3D "C:\Storage.XLS"
> > > >> > End If
>
> > > >> > XLName =3D Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath,
> > > >> > "\"))
> > > > '
> > > >> > File Name
>
> > > >> > If Dir(ExcelPath) <> XLName Then ' Not found
> > > >> > XLFilePath =3D FindFile("C:\Documents and Settings\Phil\My
> > > >> > Documents\Access\MDB\WFYC\", XLName)
> > > >> > Msg =3D "The name of the file you have selected is " & vbCrLf
> > > >> > Msg =3D Msg & XLFilePath & vbCrLf
> > > >> > Msg =3D Msg & "but the original file was " & vbCrLf
> > > >> > Msg =3D Msg & ExcelPath & vbCrLf
> > > >> > Msg =3D Msg & "Do you want to use the new name in future?"
> > > >> > If MsgBox(Msg, vbQuestion + vbYesNo) =3D vbYes Then
> > > >> > ExcelPath =3D XLFilePath
> > > >> > End If
> > > >> > End If
>
> > > >> > Set ExcelApp =3D CreateObject("Excel.Application")
>
> > > >> > If Err.Number <> 0 Then ExcelWasNotRunning =3D True
> > > >> > Err.Clear ' Clear Err object in case error occurred.
>
> > > >> > ' Set the object variable to reference the file you want to see.
>
> > > >> > Set ExcelApp =3D CreateObject("Excel.Application")
> > > >> > ExcelApp.WorkBooks.Open ExcelPath, , True ' Read
> > > > Only
> > > >> > ExcelApp.Visible =3D True
>
> > > >> > Exit_Storage_Click:
> > > >> > Exit Sub
>
> > > >> > Err_Storage_Click:
> > > >> > If Err =3D 2447 Then ' Corrupted File name (with # sign)
> > > >> > Resume Next
> > > >> > Else
> > > >> > MsgBox Err.Description
> > > >> > Resume Exit_Storage_Click
> > > >> > End If
>
> > > >> > End Sub
>
> > > >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver
> > > >> > Login
> > > >> > Failed" and the message "The database has been placed in a state =
by
> > > >> > user
> > > >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened o=
r
> > > > locked"
>
> > > >> > What am I doing wrong
>
> > > >> > Thank
>
> > > >> > Phil- Hide quoted text -
>
> > > - Show quoted text -
>
> > what's the name of the table being selected in the mdbname ?
> > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
> > YPos,
> > XLabelPosition, YLabelPosition, LabelAngle "
> > SQLStg =3D SQLStg & "FROM `" & MDBName & "`." ' Note the `
> > symbol.
> > Significant- Hide quoted text -
>
> > - Show quoted text -
>
> ok.. using access97, I've got a test mdb with a table called tblMonth
> I created an xls with the function you have above, except I changed
> this
> =A0 > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName,
> =A0 XPos, YPos,
> =A0 > > XLabelPosition, YLabelPosition, LabelAngle "
> =A0 > > SQLStg =3D SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.=

> =A0 > > Significant
>
> to sqlstg =3D "select * from tblMonth"
>
> and this
> =A0 > > .CommandText =3D Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> =A0 > > ORDER BY TypeOfSpace, Space")
>
> to .commandtext =3D array(sqlstg)
>
> and stepped through the function and got the external data I expected
>
> I saved and closed excel, opened my mdb, made of change to a record
> leaving the mdb open, I opened excel, hit refresh, and got the changed
> record's data
>
> closed excel, changed another record, opened the form, click the
> button that has your code,
> it opened excel no problem and refreshing the data showed the correct
> data
>
> I wonder if this is because you're using an MDE ?- Hide quoted text -
>
> - Show quoted text -

and if you open the MDE, and then manually open the excel worksheet,
excel can access the data without the error ?

if so, and my little test worked, there's got to be something else in
the MDE causing the error

the only way I get an 'exclusively locked' error is by opening the
table in design mode prior to opening excel, and even then excel
opens, I get the error when trying to refresh the data

it opens fine when I have a dirty record
and when the form uses the table as a recordsource

I don't know where else to look
0
lesperancer (750)
5/28/2008 10:21:18 AM
Hi again Roger

This is getting really really weird.

I have created a new database with 2 tables - Town & County and 1 query 
linking them Query1  ... keep it simple

I have a form with a textbox to hold the Excel file name and a command 
button to open the file. The form and Textbox are unbound

Also created Book1.XLS that gets it's data from Query 1

If I open the form and press the command button everything works as it 
should, but here's the weird bit. If I open the form in design view, do 
nothing to it, then change to form view I get the same error. It is if 
Access is setting a flag somewhere.

Seems similar to what you were getting.

Is this an AK2 Bug (or Excel Bug) ??

Phil

"Roger" <lesperancer@natpro.com> wrote in message 
news:aaf18c59-71a8-4823-9ffd-6c550a6af895@x41g2000hsb.googlegroups.com...
On May 27, 3:09 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Hi Rger
>
> Tried changing it to an MDB but no luck. It's a back end front end DB, so
> the Front end, wher it is an Mdb or Mde is still looking at the same 
> tables
>
> Thanks
>
> "Roger" <lesperan...@natpro.com> wrote in message
>
> news:83ba424f-d177-4609-95d3-fca008d5963f@y38g2000hsy.googlegroups.com...
> On May 26, 3:33 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
>
>
>
>
> > Hi Roger,
>
> > Thought you might be on to something, so rewrote the GetAccess() module
> > which still works. Bit more readable as all the examples of the arrays 
> > in
> > the AddConnection line were a foul mess.
>
> > Still same problem though
>
> > Phil
>
> > Function GetAccess()
> > '
> > ' Macro1 Macro
> > ' Macro recorded 26/03/2008 by Phil Stanton
> > '
> > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > On Error GoTo GetAccess_Err
>
> > Worksheets("Linked Data").Activate
> > With ActiveSheet
> > MDBName = .Range("A1")
>
> > CheckFile:
> > If Dir(MDBName) = "" Then ' Not found
> > MDBName = Application.GetOpenFileName("Access Database ,*.mde",
> > , "Where is the Club Database?")
> > If MsgBox("Do you want to use this database in future?",
> > vbQuestion + vbYesNo) = vbYes Then
> > .Range("A1") = MDBName
> > End If
> > End If
> > End With
>
> > '
> > ' Clear Cells
>
> > ActiveSheet.Range("A2:H300").ClearContents
>
> > SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YPos,
> > XLabelPosition, YLabelPosition, LabelAngle "
> > SQLStg = SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, Space"
>
> > DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))
>
> > With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS
> > Access Database;"), _
> > Array("DBQ=" & MDBName & ";"), _
> > Array("DefaultDir=" & DefaultDirectory & ";DriverId=25;"), _
> > Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
> > )), Destination:=Range("A2"))
> > .CommandText = Array(SQLStg)
> > .Name = "Query from MS Access Database"
> > .FieldNames = True
> > .RowNumbers = False
> > .FillAdjacentFormulas = False
> > .PreserveFormatting = True
> > .RefreshOnFileOpen = False
> > .BackgroundQuery = True
> > .RefreshStyle = xlOverwriteCells
> > .SavePassword = True
> > .SaveData = True
> > .AdjustColumnWidth = True
> > .RefreshPeriod = 0
> > .PreserveColumnInfo = True
> > .Refresh BackgroundQuery:=True
> > End With
>
> > Exit Function
>
> > GetAccess_Err:
> > Stop
> > If Err = 12 Then
> > ThisWorkbook.Close , False
> > Else
> > MsgBox Err.Description
> > End If
>
> > End Function"Roger" <lesperan...@natpro.com> wrote in message
>
> >news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.com...
> > On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > Thanks for coming back
>
> > > Code is:-
> > > Function GetAccess()
> > > '
> > > ' Macro1 Macro
> > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > '
> > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > On Error GoTo GetAccess_Err
>
> > > Worksheets("Linked Data").Activate
> > > With ActiveSheet
> > > MDBName = .Range("A1")
>
> > > CheckFile:
> > > If Dir(MDBName) = "" Then ' Not found
> > > MDBName = Application.GetOpenFileName("Access Database ,*.mde",
> > > , "Where is the Club Database?")
> > > If MsgBox("Do you want to use this database in future?",
> > > vbQuestion + vbYesNo) = vbYes Then
> > > .Range("A1") = MDBName
> > > End If
> > > End If
> > > End With
>
> > > '
> > > ' Clear Cells
>
> > > ActiveSheet.Range("A2:H300").Clear
>
> > > SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, 
> > > YPos,
> > > XLabelPosition, YLabelPosition, LabelAngle "
> > > SQLStg = SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> > > Significant
>
> > > DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))
>
> > > 'DBName = "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
> > > '"ODBC;DSN=MS Access Database;DBQ=" & MDBName &
> > > ";DefaultDir=C:\WFYC Data\Phil's Folder\Acces"
> > > With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
> > > "ODBC;DSN=MS Access Database;DBQ=" & MDBName & ";DefaultDir=" &
> > > DefaultDirectory _
> > > ), Array("s\MDB;DriverId=25;FIL=MS
> > > Access;MaxBufferSize=2048;PageTimeout=5;")), _
> > > Destination:=Range("A2"))
> > > .CommandText = Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > > ORDER BY TypeOfSpace, Space")
> > > .Name = "Query from MS Access Database"
> > > .FieldNames = True
> > > .RowNumbers = False
> > > .FillAdjacentFormulas = False
> > > .PreserveFormatting = True
> > > .RefreshOnFileOpen = False
> > > .BackgroundQuery = True
> > > .RefreshStyle = xlOverwriteCells
> > > .SavePassword = True
> > > .SaveData = True
> > > .AdjustColumnWidth = True
> > > .RefreshPeriod = 0
> > > .PreserveColumnInfo = True
> > > .Refresh BackgroundQuery:=True
> > > End With
>
> > > 'Stop ' Has it got here?
>
> > > Exit Function
>
> > > GetAccess_Err:
> > > Stop
> > > If Err = 1234 Then ' No Idea what the error
> > > code will be
> > > ThisWorkbook.Close , False
> > > Else
> > > MsgBox Err.Description
> > > End If
>
> > > End Function
>
> > > There is a lot of other code, but this is the bit where the data is
> > > "extracted" from Access.
> > > As I said, no problem whether the Access DB is open or not, the 
> > > problem
> > > comes when I click the button on Access form to show the Excel file.
> > > The "Linked Data" worksheet holds and processes the data, and the 
> > > output
> > > is
> > > 11 labelled scatter charts, one on each page (Map of boat compound and
> > > where
> > > each boat is stored)
>
> > > Thanks again
>
> > > Phil
>
> > > "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> > >news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
> > > > Please post the code that is in the Excel file. If that code refers 
> > > > to
> > > > the
> > > > database, then that is most likely where the error is occuring, in
> > > > which
> > > > case, we might be better able to help you.
>
> > > > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> > > >> Sorry
>
> > > >> Should have added
>
> > > >> There is some code in the Excel application which refers to the
> > > >> Database
> > > > and
> > > >> extracts the relevant information
>
> > > >> No problem opening Excel whether the database is open or not.
>
> > > >> Phil
>
> > > >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> > > >> >I have a form with a button which is supposed to open an Excel 
> > > >> >file
> > > >> >(With
> > > >> >lots of Macros /VBA) in it. The Excel file gets it's data from the
> > > >> >Access
> > > >> >program
>
> > > >> > Here is the code
>
> > > >> > Private Sub Storage_Click()
>
> > > >> > On Error GoTo Err_Storage_Click
>
> > > >> > Dim ExcelApp As Object
> > > >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> > > >> > Dim XLFilePath As String
> > > >> > Dim XLName As String ' Excel file name from
> > > >> > Paths
> > > >> > Dim MyDb As Database
> > > >> > Dim Msg As String
>
> > > >> > ' Find the normal path
> > > >> > ' Folder and File
> > > >> > If Nz(ExcelPath) = "" Then
> > > >> > ExcelPath = "C:\Storage.XLS"
> > > >> > End If
>
> > > >> > XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath,
> > > >> > "\"))
> > > > '
> > > >> > File Name
>
> > > >> > If Dir(ExcelPath) <> XLName Then ' Not found
> > > >> > XLFilePath = FindFile("C:\Documents and Settings\Phil\My
> > > >> > Documents\Access\MDB\WFYC\", XLName)
> > > >> > Msg = "The name of the file you have selected is " & vbCrLf
> > > >> > Msg = Msg & XLFilePath & vbCrLf
> > > >> > Msg = Msg & "but the original file was " & vbCrLf
> > > >> > Msg = Msg & ExcelPath & vbCrLf
> > > >> > Msg = Msg & "Do you want to use the new name in future?"
> > > >> > If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
> > > >> > ExcelPath = XLFilePath
> > > >> > End If
> > > >> > End If
>
> > > >> > Set ExcelApp = CreateObject("Excel.Application")
>
> > > >> > If Err.Number <> 0 Then ExcelWasNotRunning = True
> > > >> > Err.Clear ' Clear Err object in case error occurred.
>
> > > >> > ' Set the object variable to reference the file you want to see.
>
> > > >> > Set ExcelApp = CreateObject("Excel.Application")
> > > >> > ExcelApp.WorkBooks.Open ExcelPath, , True ' Read
> > > > Only
> > > >> > ExcelApp.Visible = True
>
> > > >> > Exit_Storage_Click:
> > > >> > Exit Sub
>
> > > >> > Err_Storage_Click:
> > > >> > If Err = 2447 Then ' Corrupted File name (with # sign)
> > > >> > Resume Next
> > > >> > Else
> > > >> > MsgBox Err.Description
> > > >> > Resume Exit_Storage_Click
> > > >> > End If
>
> > > >> > End Sub
>
> > > >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver
> > > >> > Login
> > > >> > Failed" and the message "The database has been placed in a state 
> > > >> > by
> > > >> > user
> > > >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened 
> > > >> > or
> > > > locked"
>
> > > >> > What am I doing wrong
>
> > > >> > Thank
>
> > > >> > Phil- Hide quoted text -
>
> > > - Show quoted text -
>
> > what's the name of the table being selected in the mdbname ?
> > SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
> > YPos,
> > XLabelPosition, YLabelPosition, LabelAngle "
> > SQLStg = SQLStg & "FROM `" & MDBName & "`." ' Note the `
> > symbol.
> > Significant- Hide quoted text -
>
> > - Show quoted text -
>
> ok.. using access97, I've got a test mdb with a table called tblMonth
> I created an xls with the function you have above, except I changed
> this
> > > SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName,
> XPos, YPos,
> > > XLabelPosition, YLabelPosition, LabelAngle "
> > > SQLStg = SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> > > Significant
>
> to sqlstg = "select * from tblMonth"
>
> and this
> > > .CommandText = Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > > ORDER BY TypeOfSpace, Space")
>
> to .commandtext = array(sqlstg)
>
> and stepped through the function and got the external data I expected
>
> I saved and closed excel, opened my mdb, made of change to a record
> leaving the mdb open, I opened excel, hit refresh, and got the changed
> record's data
>
> closed excel, changed another record, opened the form, click the
> button that has your code,
> it opened excel no problem and refreshing the data showed the correct
> data
>
> I wonder if this is because you're using an MDE ?- Hide quoted text -
>
> - Show quoted text -

and if you open the MDE, and then manually open the excel worksheet,
excel can access the data without the error ?

if so, and my little test worked, there's got to be something else in
the MDE causing the error

the only way I get an 'exclusively locked' error is by opening the
table in design mode prior to opening excel, and even then excel
opens, I get the error when trying to refresh the data

it opens fine when I have a dirty record
and when the form uses the table as a recordsource

I don't know where else to look 


0
phil126 (320)
5/28/2008 10:09:44 PM
Unless I am mistaken you are trying to use a circular reference
(access -> excel -> access).

It SHOULD NOT work and to the best of my knowledge will not work in
any programming environment.


On May 28, 6:09=A0pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Hi again Roger
>
> This is getting really really weird.
>
> I have created a new database with 2 tables - Town & County and 1 query
> linking them Query1 =A0... keep it simple
>
> I have a form with a textbox to hold the Excel file name and a command
> button to open the file. The form and Textbox are unbound
>
> Also created Book1.XLS that gets it's data from Query 1
>
> If I open the form and press the command button everything works as it
> should, but here's the weird bit. If I open the form in design view, do
> nothing to it, then change to form view I get the same error. It is if
> Access is setting a flag somewhere.
>
> Seems similar to what you were getting.
>
> Is this an AK2 Bug (or Excel Bug) ??
>
> Phil
>
> "Roger" <lesperan...@natpro.com> wrote in message
>
> news:aaf18c59-71a8-4823-9ffd-6c550a6af895@x41g2000hsb.googlegroups.com...
> On May 27, 3:09 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
>
>
> > Hi Rger
>
> > Tried changing it to an MDB but no luck. It's a back end front end DB, s=
o
> > the Front end, wher it is an Mdb or Mde is still looking at the same
> > tables
>
> > Thanks
>
> > "Roger" <lesperan...@natpro.com> wrote in message
>
> >news:83ba424f-d177-4609-95d3-fca008d5963f@y38g2000hsy.googlegroups.com...=

> > On May 26, 3:33 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > Hi Roger,
>
> > > Thought you might be on to something, so rewrote the GetAccess() modul=
e
> > > which still works. Bit more readable as all the examples of the arrays=

> > > in
> > > the AddConnection line were a foul mess.
>
> > > Still same problem though
>
> > > Phil
>
> > > Function GetAccess()
> > > '
> > > ' Macro1 Macro
> > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > '
> > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > On Error GoTo GetAccess_Err
>
> > > Worksheets("Linked Data").Activate
> > > With ActiveSheet
> > > MDBName =3D .Range("A1")
>
> > > CheckFile:
> > > If Dir(MDBName) =3D "" Then ' Not found
> > > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > > , "Where is the Club Database?")
> > > If MsgBox("Do you want to use this database in future?",
> > > vbQuestion + vbYesNo) =3D vbYes Then
> > > .Range("A1") =3D MDBName
> > > End If
> > > End If
> > > End With
>
> > > '
> > > ' Clear Cells
>
> > > ActiveSheet.Range("A2:H300").ClearContents
>
> > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YP=
os,
> > > XLabelPosition, YLabelPosition, LabelAngle "
> > > SQLStg =3D SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, Space=
"
>
> > > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array("ODBC;DSN=
=3DMS
> > > Access Database;"), _
> > > Array("DBQ=3D" & MDBName & ";"), _
> > > Array("DefaultDir=3D" & DefaultDirectory & ";DriverId=3D25;"), _
> > > Array("FIL=3DMS Access;MaxBufferSize=3D2048;PageTimeout=3D5;" _
> > > )), Destination:=3DRange("A2"))
> > > .CommandText =3D Array(SQLStg)
> > > .Name =3D "Query from MS Access Database"
> > > .FieldNames =3D True
> > > .RowNumbers =3D False
> > > .FillAdjacentFormulas =3D False
> > > .PreserveFormatting =3D True
> > > .RefreshOnFileOpen =3D False
> > > .BackgroundQuery =3D True
> > > .RefreshStyle =3D xlOverwriteCells
> > > .SavePassword =3D True
> > > .SaveData =3D True
> > > .AdjustColumnWidth =3D True
> > > .RefreshPeriod =3D 0
> > > .PreserveColumnInfo =3D True
> > > .Refresh BackgroundQuery:=3DTrue
> > > End With
>
> > > Exit Function
>
> > > GetAccess_Err:
> > > Stop
> > > If Err =3D 12 Then
> > > ThisWorkbook.Close , False
> > > Else
> > > MsgBox Err.Description
> > > End If
>
> > > End Function"Roger" <lesperan...@natpro.com> wrote in message
>
> > >news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.com.=
...
> > > On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > > Thanks for coming back
>
> > > > Code is:-
> > > > Function GetAccess()
> > > > '
> > > > ' Macro1 Macro
> > > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > > '
> > > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > > On Error GoTo GetAccess_Err
>
> > > > Worksheets("Linked Data").Activate
> > > > With ActiveSheet
> > > > MDBName =3D .Range("A1")
>
> > > > CheckFile:
> > > > If Dir(MDBName) =3D "" Then ' Not found
> > > > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > > > , "Where is the Club Database?")
> > > > If MsgBox("Do you want to use this database in future?",
> > > > vbQuestion + vbYesNo) =3D vbYes Then
> > > > .Range("A1") =3D MDBName
> > > > End If
> > > > End If
> > > > End With
>
> > > > '
> > > > ' Clear Cells
>
> > > > ActiveSheet.Range("A2:H300").Clear
>
> > > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
> > > > YPos,
> > > > XLabelPosition, YLabelPosition, LabelAngle "
> > > > SQLStg =3D SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> > > > Significant
>
> > > > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > > > 'DBName =3D "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
> > > > '"ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName &
> > > > ";DefaultDir=3DC:\WFYC Data\Phil's Folder\Acces"
> > > > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array( _
> > > > "ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName & ";DefaultDir=3D" =
&
> > > > DefaultDirectory _
> > > > ), Array("s\MDB;DriverId=3D25;FIL=3DMS
> > > > Access;MaxBufferSize=3D2048;PageTimeout=3D5;")), _
> > > > Destination:=3DRange("A2"))
> > > > .CommandText =3D Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > > > ORDER BY TypeOfSpace, Space")
> > > > .Name =3D "Query from MS Access Database"
> > > > .FieldNames =3D True
> > > > .RowNumbers =3D False
> > > > .FillAdjacentFormulas =3D False
> > > > .PreserveFormatting =3D True
> > > > .RefreshOnFileOpen =3D False
> > > > .BackgroundQuery =3D True
> > > > .RefreshStyle =3D xlOverwriteCells
> > > > .SavePassword =3D True
> > > > .SaveData =3D True
> > > > .AdjustColumnWidth =3D True
> > > > .RefreshPeriod =3D 0
> > > > .PreserveColumnInfo =3D True
> > > > .Refresh BackgroundQuery:=3DTrue
> > > > End With
>
> > > > 'Stop ' Has it got here?
>
> > > > Exit Function
>
> > > > GetAccess_Err:
> > > > Stop
> > > > If Err =3D 1234 Then ' No Idea what the error
> > > > code will be
> > > > ThisWorkbook.Close , False
> > > > Else
> > > > MsgBox Err.Description
> > > > End If
>
> > > > End Function
>
> > > > There is a lot of other code, but this is the bit where the data is
> > > > "extracted" from Access.
> > > > As I said, no problem whether the Access DB is open or not, the
> > > > problem
> > > > comes when I click the button on Access form to show the Excel file.=

> > > > The "Linked Data" worksheet holds and processes the data, and the
> > > > output
> > > > is
> > > > 11 labelled scatter charts, one on each page (Map of boat compound a=
nd
> > > > where
> > > > each boat is stored)
>
> > > > Thanks again
>
> > > > Phil
>
> > > > "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> > > >news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
> > > > > Please post the code that is in the Excel file. If that code refer=
s
> > > > > to
> > > > > the
> > > > > database, then that is most likely where the error is occuring, in=

> > > > > which
> > > > > case, we might be better able to help you.
>
> > > > > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > > >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> > > > >> Sorry
>
> > > > >> Should have added
>
> > > > >> There is some code in the Excel application which refers to the
> > > > >> Database
> > > > > and
> > > > >> extracts the relevant information
>
> > > > >> No problem opening Excel whether the database is open or not.
>
> > > > >> Phil
>
> > > > >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > > >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> > > > >> >I have a form with a button which is supposed to open an Excel
> > > > >> >file
> > > > >> >(With
> > > > >> >lots of Macros /VBA) in it. The Excel file gets it's data from t=
he
> > > > >> >Access
> > > > >> >program
>
> > > > >> > Here is the code
>
> > > > >> > Private Sub Storage_Click()
>
> > > > >> > On Error GoTo Err_Storage_Click
>
> > > > >> > Dim ExcelApp As Object
> > > > >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> > > > >> > Dim XLFilePath As String
> > > > >> > Dim XLName As String ' Excel file name from
> > > > >> > Paths
> > > > >> > Dim MyDb As Database
> > > > >> > Dim Msg As String
>
> > > > >> > ' Find the normal path
> > > > >> > ' Folder and File
> > > > >> > If Nz(ExcelPath) =3D "" Then
> > > > >> > ExcelPath =3D "C:\Storage.XLS"
> > > > >> > End If
>
> > > > >> > XLName =3D Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath=
,
> > > > >> > "\"))
> > > > > '
> > > > >> > File Name
>
> > > > >> > If Dir(ExcelPath) <> XLName Then ' Not found
> > > > >> > XLFilePath =3D FindFile("C:\Documents and Settings\Phil\My
> > > > >> > Documents\Access\MDB\WFYC\", XLName)
> > > > >> > Msg =3D "The name of the file you have selected is " & vbCrLf
> > > > >> > Msg =3D Msg & XLFilePath & vbCrLf
> > > > >> > Msg =3D Msg & "but the original file was " & vbCrLf
> > > > >> > Msg =3D Msg & ExcelPath & vbCrLf
> > > > >> > Msg =3D Msg & "Do you want to use the new name in future?"
> > > > >> > If MsgBox(Msg, vbQuestion + vbYesNo) =3D vbYes Then
> > > > >> > ExcelPath =3D XLFilePath
> > > > >> > End If
> > > > >> > End If
>
> > > > >> > Set ExcelApp =3D CreateObject("Excel.Application")
>
> > > > >> > If Err.Number <> 0 Then ExcelWasNotRunning =3D True
> > > > >> > Err.Clear ' Clear Err object in case error occurred.
>
> > > > >> > ' Set the object variable to reference the file you want to see=
..
>
> > > > >> > Set ExcelApp =3D CreateObject("Excel.Application")
> > > > >> > ExcelApp.WorkBooks.Open ExcelPath, , True ' Read
> > > > > Only
> > > > >> > ExcelApp.Visible =3D True
>
> > > > >> > Exit_Storage_Click:
> > > > >> > Exit Sub
>
> > > > >> > Err_Storage_Click:
> > > > >> > If Err =3D 2447 Then ' Corrupted File name (with # sign)
> > > > >> > Resume Next
> > > > >> > Else
> > > > >> > MsgBox Err.Description
> > > > >> > Resume Exit_Storage_Click
> > > > >> > End If
>
> > > > >> > End Sub
>
> > > > >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver=

> > > > >> > Login
> > > > >> > Failed" and the message "The database has been placed in a stat=
e
> > > > >> > by
> > > > >> > user
> > > > >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened=

> > > > >> > or
> > > > > locked"
>
> > > > >> > What am I doing
>
> ...
>
> read more =BB

0
5/28/2008 10:33:59 PM
Hi Lyle

See my previous post. It always works providing I don't open the Access form 
in design view first or view the code.

Another interesting thing is even in form view, if I have the property box 
open (The form is set to AllowDesignChanges All Views), if I open the code 
window to the OnClick event of the command butoon, again it fails.

Db is about 180 k and Excel file about 50k. Could send them to you if you 
are interested. (Office 2000 SP3)

Phil


"lyle fairfield" <lyle.fairfield@gmail.com> wrote in message
news:b962f624-3a09-44c6-9539-b6ff17e043e1@c58g2000hsc.googlegroups.com...
Unless I am mistaken you are trying to use a circular reference
(access -> excel -> access).

It SHOULD NOT work and to the best of my knowledge will not work in
any programming environment.


On May 28, 6:09 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Hi again Roger
>
> This is getting really really weird.
>
> I have created a new database with 2 tables - Town & County and 1 query
> linking them Query1 ... keep it simple
>
> I have a form with a textbox to hold the Excel file name and a command
> button to open the file. The form and Textbox are unbound
>
> Also created Book1.XLS that gets it's data from Query 1
>
> If I open the form and press the command button everything works as it
> should, but here's the weird bit. If I open the form in design view, do
> nothing to it, then change to form view I get the same error. It is if
> Access is setting a flag somewhere.
>
> Seems similar to what you were getting.
>
> Is this an AK2 Bug (or Excel Bug) ??
>
> Phil
>
> "Roger" <lesperan...@natpro.com> wrote in message
>
> news:aaf18c59-71a8-4823-9ffd-6c550a6af895@x41g2000hsb.googlegroups.com...
> On May 27, 3:09 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
>
>
> > Hi Rger
>
> > Tried changing it to an MDB but no luck. It's a back end front end DB,
> > so
> > the Front end, wher it is an Mdb or Mde is still looking at the same
> > tables
>
> > Thanks
>
> > "Roger" <lesperan...@natpro.com> wrote in message
>
> >news:83ba424f-d177-4609-95d3-fca008d5963f@y38g2000hsy.googlegroups.com...
> > On May 26, 3:33 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > Hi Roger,
>
> > > Thought you might be on to something, so rewrote the GetAccess()
> > > module
> > > which still works. Bit more readable as all the examples of the arrays
> > > in
> > > the AddConnection line were a foul mess.
>
> > > Still same problem though
>
> > > Phil
>
> > > Function GetAccess()
> > > '
> > > ' Macro1 Macro
> > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > '
> > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > On Error GoTo GetAccess_Err
>
> > > Worksheets("Linked Data").Activate
> > > With ActiveSheet
> > > MDBName = .Range("A1")
>
> > > CheckFile:
> > > If Dir(MDBName) = "" Then ' Not found
> > > MDBName = Application.GetOpenFileName("Access Database ,*.mde",
> > > , "Where is the Club Database?")
> > > If MsgBox("Do you want to use this database in future?",
> > > vbQuestion + vbYesNo) = vbYes Then
> > > .Range("A1") = MDBName
> > > End If
> > > End If
> > > End With
>
> > > '
> > > ' Clear Cells
>
> > > ActiveSheet.Range("A2:H300").ClearContents
>
> > > SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
> > > YPos,
> > > XLabelPosition, YLabelPosition, LabelAngle "
> > > SQLStg = SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, Space"
>
> > > DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))
>
> > > With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS
> > > Access Database;"), _
> > > Array("DBQ=" & MDBName & ";"), _
> > > Array("DefaultDir=" & DefaultDirectory & ";DriverId=25;"), _
> > > Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
> > > )), Destination:=Range("A2"))
> > > .CommandText = Array(SQLStg)
> > > .Name = "Query from MS Access Database"
> > > .FieldNames = True
> > > .RowNumbers = False
> > > .FillAdjacentFormulas = False
> > > .PreserveFormatting = True
> > > .RefreshOnFileOpen = False
> > > .BackgroundQuery = True
> > > .RefreshStyle = xlOverwriteCells
> > > .SavePassword = True
> > > .SaveData = True
> > > .AdjustColumnWidth = True
> > > .RefreshPeriod = 0
> > > .PreserveColumnInfo = True
> > > .Refresh BackgroundQuery:=True
> > > End With
>
> > > Exit Function
>
> > > GetAccess_Err:
> > > Stop
> > > If Err = 12 Then
> > > ThisWorkbook.Close , False
> > > Else
> > > MsgBox Err.Description
> > > End If
>
> > > End Function"Roger" <lesperan...@natpro.com> wrote in message
>
> > >news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.com...
> > > On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > > Thanks for coming back
>
> > > > Code is:-
> > > > Function GetAccess()
> > > > '
> > > > ' Macro1 Macro
> > > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > > '
> > > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > > On Error GoTo GetAccess_Err
>
> > > > Worksheets("Linked Data").Activate
> > > > With ActiveSheet
> > > > MDBName = .Range("A1")
>
> > > > CheckFile:
> > > > If Dir(MDBName) = "" Then ' Not found
> > > > MDBName = Application.GetOpenFileName("Access Database ,*.mde",
> > > > , "Where is the Club Database?")
> > > > If MsgBox("Do you want to use this database in future?",
> > > > vbQuestion + vbYesNo) = vbYes Then
> > > > .Range("A1") = MDBName
> > > > End If
> > > > End If
> > > > End With
>
> > > > '
> > > > ' Clear Cells
>
> > > > ActiveSheet.Range("A2:H300").Clear
>
> > > > SQLStg = "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
> > > > YPos,
> > > > XLabelPosition, YLabelPosition, LabelAngle "
> > > > SQLStg = SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> > > > Significant
>
> > > > DefaultDirectory = Left(MDBName, InStrRev(MDBName, "\"))
>
> > > > 'DBName = "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
> > > > '"ODBC;DSN=MS Access Database;DBQ=" & MDBName &
> > > > ";DefaultDir=C:\WFYC Data\Phil's Folder\Acces"
> > > > With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
> > > > "ODBC;DSN=MS Access Database;DBQ=" & MDBName & ";DefaultDir=" &
> > > > DefaultDirectory _
> > > > ), Array("s\MDB;DriverId=25;FIL=MS
> > > > Access;MaxBufferSize=2048;PageTimeout=5;")), _
> > > > Destination:=Range("A2"))
> > > > .CommandText = Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > > > ORDER BY TypeOfSpace, Space")
> > > > .Name = "Query from MS Access Database"
> > > > .FieldNames = True
> > > > .RowNumbers = False
> > > > .FillAdjacentFormulas = False
> > > > .PreserveFormatting = True
> > > > .RefreshOnFileOpen = False
> > > > .BackgroundQuery = True
> > > > .RefreshStyle = xlOverwriteCells
> > > > .SavePassword = True
> > > > .SaveData = True
> > > > .AdjustColumnWidth = True
> > > > .RefreshPeriod = 0
> > > > .PreserveColumnInfo = True
> > > > .Refresh BackgroundQuery:=True
> > > > End With
>
> > > > 'Stop ' Has it got here?
>
> > > > Exit Function
>
> > > > GetAccess_Err:
> > > > Stop
> > > > If Err = 1234 Then ' No Idea what the error
> > > > code will be
> > > > ThisWorkbook.Close , False
> > > > Else
> > > > MsgBox Err.Description
> > > > End If
>
> > > > End Function
>
> > > > There is a lot of other code, but this is the bit where the data is
> > > > "extracted" from Access.
> > > > As I said, no problem whether the Access DB is open or not, the
> > > > problem
> > > > comes when I click the button on Access form to show the Excel file.
> > > > The "Linked Data" worksheet holds and processes the data, and the
> > > > output
> > > > is
> > > > 11 labelled scatter charts, one on each page (Map of boat compound
> > > > and
> > > > where
> > > > each boat is stored)
>
> > > > Thanks again
>
> > > > Phil
>
> > > > "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> > > >news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
> > > > > Please post the code that is in the Excel file. If that code
> > > > > refers
> > > > > to
> > > > > the
> > > > > database, then that is most likely where the error is occuring, in
> > > > > which
> > > > > case, we might be better able to help you.
>
> > > > > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > > >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> > > > >> Sorry
>
> > > > >> Should have added
>
> > > > >> There is some code in the Excel application which refers to the
> > > > >> Database
> > > > > and
> > > > >> extracts the relevant information
>
> > > > >> No problem opening Excel whether the database is open or not.
>
> > > > >> Phil
>
> > > > >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > > >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> > > > >> >I have a form with a button which is supposed to open an Excel
> > > > >> >file
> > > > >> >(With
> > > > >> >lots of Macros /VBA) in it. The Excel file gets it's data from
> > > > >> >the
> > > > >> >Access
> > > > >> >program
>
> > > > >> > Here is the code
>
> > > > >> > Private Sub Storage_Click()
>
> > > > >> > On Error GoTo Err_Storage_Click
>
> > > > >> > Dim ExcelApp As Object
> > > > >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> > > > >> > Dim XLFilePath As String
> > > > >> > Dim XLName As String ' Excel file name from
> > > > >> > Paths
> > > > >> > Dim MyDb As Database
> > > > >> > Dim Msg As String
>
> > > > >> > ' Find the normal path
> > > > >> > ' Folder and File
> > > > >> > If Nz(ExcelPath) = "" Then
> > > > >> > ExcelPath = "C:\Storage.XLS"
> > > > >> > End If
>
> > > > >> > XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath,
> > > > >> > "\"))
> > > > > '
> > > > >> > File Name
>
> > > > >> > If Dir(ExcelPath) <> XLName Then ' Not found
> > > > >> > XLFilePath = FindFile("C:\Documents and Settings\Phil\My
> > > > >> > Documents\Access\MDB\WFYC\", XLName)
> > > > >> > Msg = "The name of the file you have selected is " & vbCrLf
> > > > >> > Msg = Msg & XLFilePath & vbCrLf
> > > > >> > Msg = Msg & "but the original file was " & vbCrLf
> > > > >> > Msg = Msg & ExcelPath & vbCrLf
> > > > >> > Msg = Msg & "Do you want to use the new name in future?"
> > > > >> > If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
> > > > >> > ExcelPath = XLFilePath
> > > > >> > End If
> > > > >> > End If
>
> > > > >> > Set ExcelApp = CreateObject("Excel.Application")
>
> > > > >> > If Err.Number <> 0 Then ExcelWasNotRunning = True
> > > > >> > Err.Clear ' Clear Err object in case error occurred.
>
> > > > >> > ' Set the object variable to reference the file you want to
> > > > >> > see.
>
> > > > >> > Set ExcelApp = CreateObject("Excel.Application")
> > > > >> > ExcelApp.WorkBooks.Open ExcelPath, , True ' Read
> > > > > Only
> > > > >> > ExcelApp.Visible = True
>
> > > > >> > Exit_Storage_Click:
> > > > >> > Exit Sub
>
> > > > >> > Err_Storage_Click:
> > > > >> > If Err = 2447 Then ' Corrupted File name (with # sign)
> > > > >> > Resume Next
> > > > >> > Else
> > > > >> > MsgBox Err.Description
> > > > >> > Resume Exit_Storage_Click
> > > > >> > End If
>
> > > > >> > End Sub
>
> > > > >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver
> > > > >> > Login
> > > > >> > Failed" and the message "The database has been placed in a
> > > > >> > state
> > > > >> > by
> > > > >> > user
> > > > >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened
> > > > >> > or
> > > > > locked"
>
> > > > >> > What am I doing
>
> ...
>
> read more �



0
phil126 (320)
5/29/2008 12:04:45 AM
On May 28, 4:09=A0pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Hi again Roger
>
> This is getting really really weird.
>
> I have created a new database with 2 tables - Town & County and 1 query
> linking them Query1 =A0... keep it simple
>
> I have a form with a textbox to hold the Excel file name and a command
> button to open the file. The form and Textbox are unbound
>
> Also created Book1.XLS that gets it's data from Query 1
>
> If I open the form and press the command button everything works as it
> should, but here's the weird bit. If I open the form in design view, do
> nothing to it, then change to form view I get the same error. It is if
> Access is setting a flag somewhere.
>
> Seems similar to what you were getting.
>
> Is this an AK2 Bug (or Excel Bug) ??
>
> Phil
>
> "Roger" <lesperan...@natpro.com> wrote in message
>
> news:aaf18c59-71a8-4823-9ffd-6c550a6af895@x41g2000hsb.googlegroups.com...
> On May 27, 3:09 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
>
>
> > Hi Rger
>
> > Tried changing it to an MDB but no luck. It's a back end front end DB, s=
o
> > the Front end, wher it is an Mdb or Mde is still looking at the same
> > tables
>
> > Thanks
>
> > "Roger" <lesperan...@natpro.com> wrote in message
>
> >news:83ba424f-d177-4609-95d3-fca008d5963f@y38g2000hsy.googlegroups.com...=

> > On May 26, 3:33 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > Hi Roger,
>
> > > Thought you might be on to something, so rewrote the GetAccess() modul=
e
> > > which still works. Bit more readable as all the examples of the arrays=

> > > in
> > > the AddConnection line were a foul mess.
>
> > > Still same problem though
>
> > > Phil
>
> > > Function GetAccess()
> > > '
> > > ' Macro1 Macro
> > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > '
> > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > On Error GoTo GetAccess_Err
>
> > > Worksheets("Linked Data").Activate
> > > With ActiveSheet
> > > MDBName =3D .Range("A1")
>
> > > CheckFile:
> > > If Dir(MDBName) =3D "" Then ' Not found
> > > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > > , "Where is the Club Database?")
> > > If MsgBox("Do you want to use this database in future?",
> > > vbQuestion + vbYesNo) =3D vbYes Then
> > > .Range("A1") =3D MDBName
> > > End If
> > > End If
> > > End With
>
> > > '
> > > ' Clear Cells
>
> > > ActiveSheet.Range("A2:H300").ClearContents
>
> > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, YP=
os,
> > > XLabelPosition, YLabelPosition, LabelAngle "
> > > SQLStg =3D SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, Space=
"
>
> > > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array("ODBC;DSN=
=3DMS
> > > Access Database;"), _
> > > Array("DBQ=3D" & MDBName & ";"), _
> > > Array("DefaultDir=3D" & DefaultDirectory & ";DriverId=3D25;"), _
> > > Array("FIL=3DMS Access;MaxBufferSize=3D2048;PageTimeout=3D5;" _
> > > )), Destination:=3DRange("A2"))
> > > .CommandText =3D Array(SQLStg)
> > > .Name =3D "Query from MS Access Database"
> > > .FieldNames =3D True
> > > .RowNumbers =3D False
> > > .FillAdjacentFormulas =3D False
> > > .PreserveFormatting =3D True
> > > .RefreshOnFileOpen =3D False
> > > .BackgroundQuery =3D True
> > > .RefreshStyle =3D xlOverwriteCells
> > > .SavePassword =3D True
> > > .SaveData =3D True
> > > .AdjustColumnWidth =3D True
> > > .RefreshPeriod =3D 0
> > > .PreserveColumnInfo =3D True
> > > .Refresh BackgroundQuery:=3DTrue
> > > End With
>
> > > Exit Function
>
> > > GetAccess_Err:
> > > Stop
> > > If Err =3D 12 Then
> > > ThisWorkbook.Close , False
> > > Else
> > > MsgBox Err.Description
> > > End If
>
> > > End Function"Roger" <lesperan...@natpro.com> wrote in message
>
> > >news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.com.=
...
> > > On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > > Thanks for coming back
>
> > > > Code is:-
> > > > Function GetAccess()
> > > > '
> > > > ' Macro1 Macro
> > > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > > '
> > > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > > On Error GoTo GetAccess_Err
>
> > > > Worksheets("Linked Data").Activate
> > > > With ActiveSheet
> > > > MDBName =3D .Range("A1")
>
> > > > CheckFile:
> > > > If Dir(MDBName) =3D "" Then ' Not found
> > > > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > > > , "Where is the Club Database?")
> > > > If MsgBox("Do you want to use this database in future?",
> > > > vbQuestion + vbYesNo) =3D vbYes Then
> > > > .Range("A1") =3D MDBName
> > > > End If
> > > > End If
> > > > End With
>
> > > > '
> > > > ' Clear Cells
>
> > > > ActiveSheet.Range("A2:H300").Clear
>
> > > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos,
> > > > YPos,
> > > > XLabelPosition, YLabelPosition, LabelAngle "
> > > > SQLStg =3D SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.
> > > > Significant
>
> > > > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > > > 'DBName =3D "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"
> > > > '"ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName &
> > > > ";DefaultDir=3DC:\WFYC Data\Phil's Folder\Acces"
> > > > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array( _
> > > > "ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName & ";DefaultDir=3D" =
&
> > > > DefaultDirectory _
> > > > ), Array("s\MDB;DriverId=3D25;FIL=3DMS
> > > > Access;MaxBufferSize=3D2048;PageTimeout=3D5;")), _
> > > > Destination:=3DRange("A2"))
> > > > .CommandText =3D Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > > > ORDER BY TypeOfSpace, Space")
> > > > .Name =3D "Query from MS Access Database"
> > > > .FieldNames =3D True
> > > > .RowNumbers =3D False
> > > > .FillAdjacentFormulas =3D False
> > > > .PreserveFormatting =3D True
> > > > .RefreshOnFileOpen =3D False
> > > > .BackgroundQuery =3D True
> > > > .RefreshStyle =3D xlOverwriteCells
> > > > .SavePassword =3D True
> > > > .SaveData =3D True
> > > > .AdjustColumnWidth =3D True
> > > > .RefreshPeriod =3D 0
> > > > .PreserveColumnInfo =3D True
> > > > .Refresh BackgroundQuery:=3DTrue
> > > > End With
>
> > > > 'Stop ' Has it got here?
>
> > > > Exit Function
>
> > > > GetAccess_Err:
> > > > Stop
> > > > If Err =3D 1234 Then ' No Idea what the error
> > > > code will be
> > > > ThisWorkbook.Close , False
> > > > Else
> > > > MsgBox Err.Description
> > > > End If
>
> > > > End Function
>
> > > > There is a lot of other code, but this is the bit where the data is
> > > > "extracted" from Access.
> > > > As I said, no problem whether the Access DB is open or not, the
> > > > problem
> > > > comes when I click the button on Access form to show the Excel file.=

> > > > The "Linked Data" worksheet holds and processes the data, and the
> > > > output
> > > > is
> > > > 11 labelled scatter charts, one on each page (Map of boat compound a=
nd
> > > > where
> > > > each boat is stored)
>
> > > > Thanks again
>
> > > > Phil
>
> > > > "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> > > >news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
> > > > > Please post the code that is in the Excel file. If that code refer=
s
> > > > > to
> > > > > the
> > > > > database, then that is most likely where the error is occuring, in=

> > > > > which
> > > > > case, we might be better able to help you.
>
> > > > > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > > >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> > > > >> Sorry
>
> > > > >> Should have added
>
> > > > >> There is some code in the Excel application which refers to the
> > > > >> Database
> > > > > and
> > > > >> extracts the relevant information
>
> > > > >> No problem opening Excel whether the database is open or not.
>
> > > > >> Phil
>
> > > > >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > > >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> > > > >> >I have a form with a button which is supposed to open an Excel
> > > > >> >file
> > > > >> >(With
> > > > >> >lots of Macros /VBA) in it. The Excel file gets it's data from t=
he
> > > > >> >Access
> > > > >> >program
>
> > > > >> > Here is the code
>
> > > > >> > Private Sub Storage_Click()
>
> > > > >> > On Error GoTo Err_Storage_Click
>
> > > > >> > Dim ExcelApp As Object
> > > > >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> > > > >> > Dim XLFilePath As String
> > > > >> > Dim XLName As String ' Excel file name from
> > > > >> > Paths
> > > > >> > Dim MyDb As Database
> > > > >> > Dim Msg As String
>
> > > > >> > ' Find the normal path
> > > > >> > ' Folder and File
> > > > >> > If Nz(ExcelPath) =3D "" Then
> > > > >> > ExcelPath =3D "C:\Storage.XLS"
> > > > >> > End If
>
> > > > >> > XLName =3D Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath=
,
> > > > >> > "\"))
> > > > > '
> > > > >> > File Name
>
> > > > >> > If Dir(ExcelPath) <> XLName Then ' Not found
> > > > >> > XLFilePath =3D FindFile("C:\Documents and Settings\Phil\My
> > > > >> > Documents\Access\MDB\WFYC\", XLName)
> > > > >> > Msg =3D "The name of the file you have selected is " & vbCrLf
> > > > >> > Msg =3D Msg & XLFilePath & vbCrLf
> > > > >> > Msg =3D Msg & "but the original file was " & vbCrLf
> > > > >> > Msg =3D Msg & ExcelPath & vbCrLf
> > > > >> > Msg =3D Msg & "Do you want to use the new name in future?"
> > > > >> > If MsgBox(Msg, vbQuestion + vbYesNo) =3D vbYes Then
> > > > >> > ExcelPath =3D XLFilePath
> > > > >> > End If
> > > > >> > End If
>
> > > > >> > Set ExcelApp =3D CreateObject("Excel.Application")
>
> > > > >> > If Err.Number <> 0 Then ExcelWasNotRunning =3D True
> > > > >> > Err.Clear ' Clear Err object in case error occurred.
>
> > > > >> > ' Set the object variable to reference the file you want to see=
..
>
> > > > >> > Set ExcelApp =3D CreateObject("Excel.Application")
> > > > >> > ExcelApp.WorkBooks.Open ExcelPath, , True ' Read
> > > > > Only
> > > > >> > ExcelApp.Visible =3D True
>
> > > > >> > Exit_Storage_Click:
> > > > >> > Exit Sub
>
> > > > >> > Err_Storage_Click:
> > > > >> > If Err =3D 2447 Then ' Corrupted File name (with # sign)
> > > > >> > Resume Next
> > > > >> > Else
> > > > >> > MsgBox Err.Description
> > > > >> > Resume Exit_Storage_Click
> > > > >> > End If
>
> > > > >> > End Sub
>
> > > > >> > When I run it, I get an Error Box "ODBC Microsoft Access Driver=

> > > > >> > Login
> > > > >> > Failed" and the message "The database has been placed in a stat=
e
> > > > >> > by
> > > > >> > user
> > > > >> > 'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened=

> > > > >> > or
> > > > > locked"
>
> > > > >> > What am I doing
>
> ...
>
> read more =BB- Hide quoted text -
>
> - Show quoted text -

I'm using office97
I opened the form in design mode, and switched to form view
and it still works....

and I don't have access2000 to test with

I don't understand why opening each application seperately works
what happens if you
   open access
   click button to open excel, and get failure

   manually open a second copy of excel... does it work or do you get
an error ?
0
lesperancer (750)
5/29/2008 1:43:20 AM
On May 28, 4:33=A0pm, lyle fairfield <lyle.fairfi...@gmail.com> wrote:
> Unless I am mistaken you are trying to use a circular reference
> (access -> excel -> access).
>
> It SHOULD NOT work and to the best of my knowledge will not work in
> any programming environment.
>
> On May 28, 6:09=A0pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
>
>
> > Hi again Roger
>
> > This is getting really really weird.
>
> > I have created a new database with 2 tables - Town & County and 1 query
> > linking them Query1 =A0... keep it simple
>
> > I have a form with a textbox to hold the Excel file name and a command
> > button to open the file. The form and Textbox are unbound
>
> > Also created Book1.XLS that gets it's data from Query 1
>
> > If I open the form and press the command button everything works as it
> > should, but here's the weird bit. If I open the form in design view, do
> > nothing to it, then change to form view I get the same error. It is if
> > Access is setting a flag somewhere.
>
> > Seems similar to what you were getting.
>
> > Is this an AK2 Bug (or Excel Bug) ??
>
> > Phil
>
> > "Roger" <lesperan...@natpro.com> wrote in message
>
> >news:aaf18c59-71a8-4823-9ffd-6c550a6af895@x41g2000hsb.googlegroups.com...=

> > On May 27, 3:09 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > Hi Rger
>
> > > Tried changing it to an MDB but no luck. It's a back end front end DB,=
 so
> > > the Front end, wher it is an Mdb or Mde is still looking at the same
> > > tables
>
> > > Thanks
>
> > > "Roger" <lesperan...@natpro.com> wrote in message
>
> > >news:83ba424f-d177-4609-95d3-fca008d5963f@y38g2000hsy.googlegroups.com.=
...
> > > On May 26, 3:33 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > > Hi Roger,
>
> > > > Thought you might be on to something, so rewrote the GetAccess() mod=
ule
> > > > which still works. Bit more readable as all the examples of the arra=
ys
> > > > in
> > > > the AddConnection line were a foul mess.
>
> > > > Still same problem though
>
> > > > Phil
>
> > > > Function GetAccess()
> > > > '
> > > > ' Macro1 Macro
> > > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > > '
> > > > Dim MDBName As String, DefaultDirectory As String, SQLStg As String
>
> > > > On Error GoTo GetAccess_Err
>
> > > > Worksheets("Linked Data").Activate
> > > > With ActiveSheet
> > > > MDBName =3D .Range("A1")
>
> > > > CheckFile:
> > > > If Dir(MDBName) =3D "" Then ' Not found
> > > > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > > > , "Where is the Club Database?")
> > > > If MsgBox("Do you want to use this database in future?",
> > > > vbQuestion + vbYesNo) =3D vbYes Then
> > > > .Range("A1") =3D MDBName
> > > > End If
> > > > End If
> > > > End With
>
> > > > '
> > > > ' Clear Cells
>
> > > > ActiveSheet.Range("A2:H300").ClearContents
>
> > > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos, =
YPos,
> > > > XLabelPosition, YLabelPosition, LabelAngle "
> > > > SQLStg =3D SQLStg & "FROM QSpaceAllocation ORDER BY TypeOfSpace, Spa=
ce"
>
> > > > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > > > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array("ODBC;DSN=
=3DMS
> > > > Access Database;"), _
> > > > Array("DBQ=3D" & MDBName & ";"), _
> > > > Array("DefaultDir=3D" & DefaultDirectory & ";DriverId=3D25;"), _
> > > > Array("FIL=3DMS Access;MaxBufferSize=3D2048;PageTimeout=3D5;" _
> > > > )), Destination:=3DRange("A2"))
> > > > .CommandText =3D Array(SQLStg)
> > > > .Name =3D "Query from MS Access Database"
> > > > .FieldNames =3D True
> > > > .RowNumbers =3D False
> > > > .FillAdjacentFormulas =3D False
> > > > .PreserveFormatting =3D True
> > > > .RefreshOnFileOpen =3D False
> > > > .BackgroundQuery =3D True
> > > > .RefreshStyle =3D xlOverwriteCells
> > > > .SavePassword =3D True
> > > > .SaveData =3D True
> > > > .AdjustColumnWidth =3D True
> > > > .RefreshPeriod =3D 0
> > > > .PreserveColumnInfo =3D True
> > > > .Refresh BackgroundQuery:=3DTrue
> > > > End With
>
> > > > Exit Function
>
> > > > GetAccess_Err:
> > > > Stop
> > > > If Err =3D 12 Then
> > > > ThisWorkbook.Close , False
> > > > Else
> > > > MsgBox Err.Description
> > > > End If
>
> > > > End Function"Roger" <lesperan...@natpro.com> wrote in message
>
> > > >news:382fc75b-fa2b-4300-a0d0-240fa5e0f047@z66g2000hsc.googlegroups.co=
m...
> > > > On May 25, 2:01 pm, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
>
> > > > > Thanks for coming back
>
> > > > > Code is:-
> > > > > Function GetAccess()
> > > > > '
> > > > > ' Macro1 Macro
> > > > > ' Macro recorded 26/03/2008 by Phil Stanton
> > > > > '
> > > > > Dim MDBName As String, DefaultDirectory As String, SQLStg As Strin=
g
>
> > > > > On Error GoTo GetAccess_Err
>
> > > > > Worksheets("Linked Data").Activate
> > > > > With ActiveSheet
> > > > > MDBName =3D .Range("A1")
>
> > > > > CheckFile:
> > > > > If Dir(MDBName) =3D "" Then ' Not found
> > > > > MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> > > > > , "Where is the Club Database?")
> > > > > If MsgBox("Do you want to use this database in future?",
> > > > > vbQuestion + vbYesNo) =3D vbYes Then
> > > > > .Range("A1") =3D MDBName
> > > > > End If
> > > > > End If
> > > > > End With
>
> > > > > '
> > > > > ' Clear Cells
>
> > > > > ActiveSheet.Range("A2:H300").Clear
>
> > > > > SQLStg =3D "SELECT DISTINCT TypeOfSpace, Space, SpaceAndName, XPos=
,
> > > > > YPos,
> > > > > XLabelPosition, YLabelPosition, LabelAngle "
> > > > > SQLStg =3D SQLStg & "FROM `" & MDBName & "`." ' Note the ` symbol.=

> > > > > Significant
>
> > > > > DefaultDirectory =3D Left(MDBName, InStrRev(MDBName, "\"))
>
> > > > > 'DBName =3D "C:\WFYC Data\Phil's Folder\Access\MDB\Clubs New 2007"=

> > > > > '"ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName &
> > > > > ";DefaultDir=3DC:\WFYC Data\Phil's Folder\Acces"
> > > > > With ActiveSheet.QueryTables.Add(Connection:=3DArray(Array( _
> > > > > "ODBC;DSN=3DMS Access Database;DBQ=3D" & MDBName & ";DefaultDir=3D=
" &
> > > > > DefaultDirectory _
> > > > > ), Array("s\MDB;DriverId=3D25;FIL=3DMS
> > > > > Access;MaxBufferSize=3D2048;PageTimeout=3D5;")), _
> > > > > Destination:=3DRange("A2"))
> > > > > .CommandText =3D Array(SQLStg, "QSpaceAllocation QSpaceAllocation
> > > > > ORDER BY TypeOfSpace, Space")
> > > > > .Name =3D "Query from MS Access Database"
> > > > > .FieldNames =3D True
> > > > > .RowNumbers =3D False
> > > > > .FillAdjacentFormulas =3D False
> > > > > .PreserveFormatting =3D True
> > > > > .RefreshOnFileOpen =3D False
> > > > > .BackgroundQuery =3D True
> > > > > .RefreshStyle =3D xlOverwriteCells
> > > > > .SavePassword =3D True
> > > > > .SaveData =3D True
> > > > > .AdjustColumnWidth =3D True
> > > > > .RefreshPeriod =3D 0
> > > > > .PreserveColumnInfo =3D True
> > > > > .Refresh BackgroundQuery:=3DTrue
> > > > > End With
>
> > > > > 'Stop ' Has it got here?
>
> > > > > Exit Function
>
> > > > > GetAccess_Err:
> > > > > Stop
> > > > > If Err =3D 1234 Then ' No Idea what the error
> > > > > code will be
> > > > > ThisWorkbook.Close , False
> > > > > Else
> > > > > MsgBox Err.Description
> > > > > End If
>
> > > > > End Function
>
> > > > > There is a lot of other code, but this is the bit where the data i=
s
> > > > > "extracted" from Access.
> > > > > As I said, no problem whether the Access DB is open or not, the
> > > > > problem
> > > > > comes when I click the button on Access form to show the Excel fil=
e.
> > > > > The "Linked Data" worksheet holds and processes the data, and the
> > > > > output
> > > > > is
> > > > > 11 labelled scatter charts, one on each page (Map of boat compound=
 and
> > > > > where
> > > > > each boat is stored)
>
> > > > > Thanks again
>
> > > > > Phil
>
> > > > > "ByteMyzer" <bNyOtSePmAyMzN...@ySaPhAoMo.com> wrote in message
>
> > > > >news:qqi_j.684$iM3.561@flpi150.ffdc.sbc.com...
>
> > > > > > Please post the code that is in the Excel file. If that code ref=
ers
> > > > > > to
> > > > > > the
> > > > > > database, then that is most likely where the error is occuring, =
in
> > > > > > which
> > > > > > case, we might be better able to help you.
>
> > > > > > "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > > > >news:R8idnaH_C8VIEaTVnZ2dnUVZ8tPinZ2d@posted.plusnet...
> > > > > >> Sorry
>
> > > > > >> Should have added
>
> > > > > >> There is some code in the Excel application which refers to the=

> > > > > >> Database
> > > > > > and
> > > > > >> extracts the relevant information
>
> > > > > >> No problem opening Excel whether the database is open or not.
>
> > > > > >> Phil
>
> > > > > >> "Phil Stanton" <p...@myfamilyname.co.uk> wrote in message
> > > > > >>news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@posted.plusnet...
> > > > > >> >I have a form with a button which is supposed to open an Excel=

> > > > > >> >file
> > > > > >> >(With
> > > > > >> >lots of Macros /VBA) in it. The Excel file gets it's data from=
 the
> > > > > >> >Access
> > > > > >> >program
>
> > > > > >> > Here is the code
>
> > > > > >> > Private Sub Storage_Click()
>
> > > > > >> > On Error GoTo Err_Storage_Click
>
> > > > > >> > Dim ExcelApp As Object
> > > > > >> > Dim ExcelWasNotRunning As Boolean ' Flag for final release
> > > > > >> > Dim XLFilePath As String
> > > > > >> > Dim XLName As String ' Excel file name from
> > > > > >> > Paths
> > > > > >> > Dim MyDb As Database
> > > > > >> > Dim Msg As String
>
> > > > > >> > ' Find the normal path
> > > > > >> > ' Folder and File
> > > > > >> > If Nz(ExcelPath) =3D "" Then
> > > > > >> > ExcelPath =3D "C:\Storage.XLS"
> > > > > >> > End If
>
> > > > > >> > XLName =3D Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPa=
th,
> > > > > >> > "\"))
> > > > > > '
> > > > > >> > File Name
>
> > > > > >> > If Dir(ExcelPath) <> XLName Then ' Not found
> > > > > >> > XLFilePath =3D FindFile("C:\Documents and Settings\Phil\My
> > > > > >> > Documents\Access\MDB\WFYC\", XLName)
> > > > > >> > Msg =3D "The name of the file you have selected is " & vbCrLf=

> > > > > >> > Msg =3D Msg & XLFilePath & vbCrLf
> > > > > >> > Msg =3D Msg & "but the original file was " & vbCrLf
> > > > > >> > Msg =3D Msg & ExcelPath & vbCrLf
> > > > > >> > Msg =3D Msg & "Do you want to use the new name in future?"
> > > > > >> > If MsgBox(Msg, vbQuestion + vbYesNo) =3D vbYes Then
> > > > > >> > ExcelPath =3D XLFilePath
> > > > > >> > End If
> > > > > >> > End If
>
> > > > > >> > Set ExcelApp =3D CreateObject("Excel.Application")
>
> > > > > >> > If Err.Number <> 0 Then ExcelWasNotRunning =3D True
> > > > > >> > Err.Clear ' Clear Err object in case error occurred.
>
> > > > > >> > ' Set the object variable to reference the file you want to s=
ee.
>
> > > > > >> > Set ExcelApp =3D CreateObject("Excel.Application")
>
> ...
>
> read more =BB- Hide quoted text -
>
> - Show quoted text -
it's not (access -> excel -> access).
it's access -> excel
     excel then has an external data source to retrieve data from an
access table

0
lesperancer (750)
5/29/2008 1:46:33 AM
On May 28, 9:46=A0pm, Roger <lesperan...@natpro.com> wrote:

> it's not (access -> excel -> access).
> it's access -> excel
> =A0 =A0 =A0excel then has an external data source to retrieve data from an=

> access table

You mean it's Access->Excel->Other Access?

I guess you are better at reading and understanding this code and
description than I.

That's not how I interpreted

"I have a form with a button which is supposed to open an Excel file
(With
lots of Macros /VBA) in it. The Excel file gets it's data from the
Access
program"

nor

"There is some code in the Excel application which refers to the
Database and
extracts the relevant information"

Does this

"MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
, "Where is the Club Database?")"

 not indicate he is looking for an instance of Access that is already
open?

And what exactly is an access table? Access has no tables.Access is a
gui that has some database manipulation commands and stores specific
procedures in JET/ACE databases.

And why when you have an instance of Access open would you open an
instance of Excel to deal with data in ANY database, either one to
which the first instance is linked, an entirely separate one, or the
first instance itself? Is there something you could do with Excel that
you couldn't do with Access/Jet etc?

When we talk of bug we can (as very recently was done in this group)
list all the conditions and steps required to emulate the bug.

I see no bug in this thread, just a display of bad code chasing an
illogical goal.







0
5/29/2008 2:45:43 AM
"lyle fairfield" <lyle.fairfield@gmail.com> wrote in message 
news:bd05b0cb-d994-4f77-8284-fa5b1e992087@a70g2000hsh.googlegroups.com...

> I see no bug in this thread, just a display of bad code chasing an
> illogical goal.

And, if you really need this data to transfer from Access to Excel, why not 
just *push* the data in from Access instead of trying to get Excel to drag 
it out?  That would seem logical to me.

Keith. 

0
here9 (1031)
5/29/2008 7:28:42 AM
On May 28, 8:45=A0pm, lyle fairfield <lyle.fairfi...@gmail.com> wrote:
> On May 28, 9:46=A0pm, Roger <lesperan...@natpro.com> wrote:
>
> > it's not (access -> excel -> access).
> > it's access -> excel
> > =A0 =A0 =A0excel then has an external data source to retrieve data from =
an
> > access table
>
> You mean it's Access->Excel->Other Access?
>
> I guess you are better at reading and understanding this code and
> description than I.
>
> That's not how I interpreted
>
> "I have a form with a button which is supposed to open an Excel file
> (With
> lots of Macros /VBA) in it. The Excel file gets it's data from the
> Access
> program"
>
> nor
>
> "There is some code in the Excel application which refers to the
> Database and
> extracts the relevant information"
>
> Does this
>
> "MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> , "Where is the Club Database?")"
>
> =A0not indicate he is looking for an instance of Access that is already
> open?
>
> And what exactly is an access table? Access has no tables.Access is a
> gui that has some database manipulation commands and stores specific
> procedures in JET/ACE databases.
>
> And why when you have an instance of Access open would you open an
> instance of Excel to deal with data in ANY database, either one to
> which the first instance is linked, an entirely separate one, or the
> first instance itself? Is there something you could do with Excel that
> you couldn't do with Access/Jet etc?
>
> When we talk of bug we can (as very recently was done in this group)
> list all the conditions and steps required to emulate the bug.
>
> I see no bug in this thread, just a display of bad code chasing an
> illogical goal.

this line
   "MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
, "Where is the Club Database?")"

simply opens a dialog box to browse for a file name, with an MDE
extension
it does not open the MDE/MDB

the getAccess function is only run once to create an external data
source to retrieve data

technically it's a jet table not an access table, so to use you above
structure
it is access -> excel -> jet

there's nothing special being done in excel, just creating a data
source and importing data into a worksheet ... which is faster than
pushing it from access
and everytime you open the worksheet, the data is automatically
refreshed

0
lesperancer (750)
5/29/2008 8:54:32 AM
Hi guys

Just to clarify. The DB is for running a Yacht/Sailing Club. We have various 
storage areas eg boat compound and pontoons, and members are charged to keep 
their boats there.

I have drawings of those areas which I hold as JPGs. I have spent ages 
working out the position of all the boats in these storage areas and given 
them an XY co-ordinate so that my Access Query (QSpaceAllocation) gives the 
name of the Storage Area, the SpaceNo, X & Y Co-ordinates, A combined field 
of the name of the person using that space, & boat name & boat class & 
whether they have paid, .

Excel,but not Access has an add-in written by Rob Bovey called "XY Chart 
Labeler" which allows you to label every point on a scatter graph.
When the Excel application opens (from the Excel Icon rather than from 
Access), it re-loads the data from the above query, checks the latest 
version of the JPG plans, updates all the scatter charts and output them as 
GIF files.

The database has various forms in it with an Image on it that retrieves the 
GIF file.

OK its a long way round, but what I am trying to achieve is to eg change the 
occupier of a space, within Access and be able to see the new plan. That is 
why I want to be able to click a button (or something) to create the new GIF 
file and load it back to the image on the form where I am changing the 
occupier.

Everything works separately, it's just the bit where I click the command 
button where I get the error. Nothing to do with record locking because it 
won't work with the space allocation form closed, and clicking a button on 
an unbound form.

Have nearly got to a solution!!!!!!

 The form checks that the Excel path is OK and loads it into the default 
value of the unbound textbox
            ExcelPath.DefaultValue = Chr$(34) & ExcelPath & Chr$(34)
            DoCmd.Save acForm, Me.Name

If I comment out the DoCmd.Save acForm, Me.Name line I at least get Excel to 
open. Still other problems there, but I think it could be caused by tryng to 
generate the graph before the data is fully loaded.

I guess the  DoCmd.Save acForm, Me.Name is a bit like opening the form in 
design view and changing back to form view

Phil





"lyle fairfield" <lyle.fairfield@gmail.com> wrote in message 
news:bd05b0cb-d994-4f77-8284-fa5b1e992087@a70g2000hsh.googlegroups.com...
On May 28, 9:46 pm, Roger <lesperan...@natpro.com> wrote:

> it's not (access -> excel -> access).
> it's access -> excel
> excel then has an external data source to retrieve data from an
> access table

You mean it's Access->Excel->Other Access?

I guess you are better at reading and understanding this code and
description than I.

That's not how I interpreted

"I have a form with a button which is supposed to open an Excel file
(With
lots of Macros /VBA) in it. The Excel file gets it's data from the
Access
program"

nor

"There is some code in the Excel application which refers to the
Database and
extracts the relevant information"

Does this

"MDBName = Application.GetOpenFileName("Access Database ,*.mde",
, "Where is the Club Database?")"

 not indicate he is looking for an instance of Access that is already
open?

And what exactly is an access table? Access has no tables.Access is a
gui that has some database manipulation commands and stores specific
procedures in JET/ACE databases.

And why when you have an instance of Access open would you open an
instance of Excel to deal with data in ANY database, either one to
which the first instance is linked, an entirely separate one, or the
first instance itself? Is there something you could do with Excel that
you couldn't do with Access/Jet etc?

When we talk of bug we can (as very recently was done in this group)
list all the conditions and steps required to emulate the bug.

I see no bug in this thread, just a display of bad code chasing an
illogical goal.








0
phil126 (320)
5/29/2008 9:23:53 AM
On May 29, 3:23=A0am, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Hi guys
>
> Just to clarify. The DB is for running a Yacht/Sailing Club. We have vario=
us
> storage areas eg boat compound and pontoons, and members are charged to ke=
ep
> their boats there.
>
> I have drawings of those areas which I hold as JPGs. I have spent ages
> working out the position of all the boats in these storage areas and given=

> them an XY co-ordinate so that my Access Query (QSpaceAllocation) gives th=
e
> name of the Storage Area, the SpaceNo, X & Y Co-ordinates, A combined fiel=
d
> of the name of the person using that space, & boat name & boat class &
> whether they have paid, .
>
> Excel,but not Access has an add-in written by Rob Bovey called "XY Chart
> Labeler" which allows you to label every point on a scatter graph.
> When the Excel application opens (from the Excel Icon rather than from
> Access), it re-loads the data from the above query, checks the latest
> version of the JPG plans, updates all the scatter charts and output them a=
s
> GIF files.
>
> The database has various forms in it with an Image on it that retrieves th=
e
> GIF file.
>
> OK its a long way round, but what I am trying to achieve is to eg change t=
he
> occupier of a space, within Access and be able to see the new plan. That i=
s
> why I want to be able to click a button (or something) to create the new G=
IF
> file and load it back to the image on the form where I am changing the
> occupier.
>
> Everything works separately, it's just the bit where I click the command
> button where I get the error. Nothing to do with record locking because it=

> won't work with the space allocation form closed, and clicking a button on=

> an unbound form.
>
> Have nearly got to a solution!!!!!!
>
> =A0The form checks that the Excel path is OK and loads it into the default=

> value of the unbound textbox
> =A0 =A0 =A0 =A0 =A0 =A0 ExcelPath.DefaultValue =3D Chr$(34) & ExcelPath & =
Chr$(34)
> =A0 =A0 =A0 =A0 =A0 =A0 DoCmd.Save acForm, Me.Name
>
> If I comment out the DoCmd.Save acForm, Me.Name line I at least get Excel =
to
> open. Still other problems there, but I think it could be caused by tryng =
to
> generate the graph before the data is fully loaded.
>
> I guess the =A0DoCmd.Save acForm, Me.Name is a bit like opening the form i=
n
> design view and changing back to form view
>
> Phil
>
> "lyle fairfield" <lyle.fairfi...@gmail.com> wrote in message
>
> news:bd05b0cb-d994-4f77-8284-fa5b1e992087@a70g2000hsh.googlegroups.com...
> On May 28, 9:46 pm, Roger <lesperan...@natpro.com> wrote:
>
> > it's not (access -> excel -> access).
> > it's access -> excel
> > excel then has an external data source to retrieve data from an
> > access table
>
> You mean it's Access->Excel->Other Access?
>
> I guess you are better at reading and understanding this code and
> description than I.
>
> That's not how I interpreted
>
> "I have a form with a button which is supposed to open an Excel file
> (With
> lots of Macros /VBA) in it. The Excel file gets it's data from the
> Access
> program"
>
> nor
>
> "There is some code in the Excel application which refers to the
> Database and
> extracts the relevant information"
>
> Does this
>
> "MDBName =3D Application.GetOpenFileName("Access Database ,*.mde",
> , "Where is the Club Database?")"
>
> =A0not indicate he is looking for an instance of Access that is already
> open?
>
> And what exactly is an access table? Access has no tables.Access is a
> gui that has some database manipulation commands and stores specific
> procedures in JET/ACE databases.
>
> And why when you have an instance of Access open would you open an
> instance of Excel to deal with data in ANY database, either one to
> which the first instance is linked, an entirely separate one, or the
> first instance itself? Is there something you could do with Excel that
> you couldn't do with Access/Jet etc?
>
> When we talk of bug we can (as very recently was done in this group)
> list all the conditions and steps required to emulate the bug.
>
> I see no bug in this thread, just a display of bad code chasing an
> illogical goal.

instead of using getAccess() to create the data source, have you tried
creating an odbc data source to your MDE, and creating the external
data query in excel

I wonder if one of the parameters in getAccess() is wrong or if one or
more are missing ?
0
lesperancer (750)
5/29/2008 9:47:25 AM
Sure, if they will run without the jpegs.

"Phil Stanton" <phil@myfamilyname.co.uk> wrote in
news:-oCdnTiQsfp9_6PVRVnygwA@posted.plusnet: 

> Db is about 180 k and Excel file about 50k. Could send them to you if
> you are interested. (Office 2000 SP3)
0
lylefa1r (282)
5/29/2008 9:58:46 AM
00ps; clicking reply, of course will fail as the address is mangled; use

lyle DOT fairfield at gmail dot com

lyle fairfield <lylefa1r@yah00.ca> wrote in news:Xns9AAD3CCC5DCD56666646261
@216.221.81.119:

> Sure, if they will run without the jpegs.
> 
> "Phil Stanton" <phil@myfamilyname.co.uk> wrote in
> news:-oCdnTiQsfp9_6PVRVnygwA@posted.plusnet: 
> 
>> Db is about 180 k and Excel file about 50k. Could send them to you if
>> you are interested. (Office 2000 SP3)

0
lylefa1r (282)
5/29/2008 10:02:05 AM
Code that opens another application and then instructs the application
to open a document is often difficult. What happens when the
application opens but the document file is not found? What happens
when the document generates an error?

I don't see that you need to control the Excel Sheet from your Access
application so I recommend that you replace the code behind the form
with:

Private Sub Storage_Click()
Application.FollowHyperlink ExcelPath.Value
End Sub
This worked swimmingly for me.

A "good" thing about Application.FollowHyperlink is that, if it can't
find the file, it doesn't open the application, but generates an
innocuous error message.

If I were doing this, I'd also include a GetOpenFileName procedure,
(plenty posted in CDMA, I think; I have one more or less ready to go
if you want it) to let your user find the excel file. You already use
this, in the reverse, in your Excel file, but excel provides this
capability implicitly with Application.GetOpenFilename; in Access you
must work for it but once importing the code, it's nothing.

Enough with gratuitous and unnecessary advice.

Bugs are replicable and can be reduced to very simple terms and
conditions. I think you have not identified a bug.
------
If you want to save the path to the excel file without messing with
saving the form you could just save the path as a property of the
Access Form Object (in CurrentProject.AllForms) as per these lines as
the code behind the form:

Private Sub Form_Load()
    On Error Resume Next
    ExcelPath.Value =3D
CurrentProject.AllForms(Me.Name).Properties("DefaultExcelPath").Value
End Sub

Private Sub Storage_Click()

With CurrentProject.AllForms(Me.Name).Properties
        .Add "DefaultExcelPath", ExcelPath.Value
        .Item("DefaultExcelPath").Value =3D ExcelPath.Value
End With

Application.FollowHyperlink ExcelPath.Value

End Sub

Now I suppose I'd better go make sure Access 2K has
CurrentProject.AllForms but TTBOMR it has.

On May 29, 5:23=A0am, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Everything works separately, it's just the bit where I click the command
> button where I get the error.
0
5/29/2008 12:25:38 PM
As I see the structure and code there are two files, an mdb and an
xls.

The cycle of activity follows:
mdb -> xls -> mdb

On May 29, 4:54=A0am, Roger <lesperan...@natpro.com> wrote:
> technically it's a jet table not an access table, so to use you above
> structure
> it is access -> excel -> jet
0
5/29/2008 12:31:12 PM
Thanks a hell of a lot, Lyle

Problem solved with the Hyperlink method

Thanks also to Roger & Keith for their help

Phil


"lyle fairfield" <lyle.fairfield@gmail.com> wrote in message 
news:0d04d146-938f-46c4-8783-92587f5b741a@j22g2000hsf.googlegroups.com...
Code that opens another application and then instructs the application
to open a document is often difficult. What happens when the
application opens but the document file is not found? What happens
when the document generates an error?

I don't see that you need to control the Excel Sheet from your Access
application so I recommend that you replace the code behind the form
with:

Private Sub Storage_Click()
Application.FollowHyperlink ExcelPath.Value
End Sub
This worked swimmingly for me.

A "good" thing about Application.FollowHyperlink is that, if it can't
find the file, it doesn't open the application, but generates an
innocuous error message.

If I were doing this, I'd also include a GetOpenFileName procedure,
(plenty posted in CDMA, I think; I have one more or less ready to go
if you want it) to let your user find the excel file. You already use
this, in the reverse, in your Excel file, but excel provides this
capability implicitly with Application.GetOpenFilename; in Access you
must work for it but once importing the code, it's nothing.

Enough with gratuitous and unnecessary advice.

Bugs are replicable and can be reduced to very simple terms and
conditions. I think you have not identified a bug.
------
If you want to save the path to the excel file without messing with
saving the form you could just save the path as a property of the
Access Form Object (in CurrentProject.AllForms) as per these lines as
the code behind the form:

Private Sub Form_Load()
    On Error Resume Next
    ExcelPath.Value =
CurrentProject.AllForms(Me.Name).Properties("DefaultExcelPath").Value
End Sub

Private Sub Storage_Click()

With CurrentProject.AllForms(Me.Name).Properties
        .Add "DefaultExcelPath", ExcelPath.Value
        .Item("DefaultExcelPath").Value = ExcelPath.Value
End With

Application.FollowHyperlink ExcelPath.Value

End Sub

Now I suppose I'd better go make sure Access 2K has
CurrentProject.AllForms but TTBOMR it has.

On May 29, 5:23 am, "Phil Stanton" <p...@myfamilyname.co.uk> wrote:
> Everything works separately, it's just the bit where I click the command
> button where I get the error. 


0
phil126 (320)
5/29/2008 7:34:48 PM
Reply: