Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

launch .mdb file and open table

Discussion in 'Information Technology' started by RB Smissaert, Jul 28, 2006.

  1. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Given I created/updated a .mdb file like with:

    Dim db As DAO.Database

    Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    or
    Set db = DBEngine.OpenDatabase(strMDBPath)

    How do I launch this .mdb file and open a specified table?
    I could do it with Shell I suppose (not sure about the table though), but
    given
    the above code there might be a better way.

    RBS
     
  2. Loading...

    Similar Threads Forum Date
    National Sikh Campaign launched to tackle misconceptions about the community Community Out-Reach Mar 16, 2014
    Punjabi University to launch Punjabipedia to promote Punjabi language Punjab, Punjabi, Punjabiyat Jan 27, 2014
    Leisure LA restaurant launches water menu, with US$20 bottles Business, Lifestyle & Leisure Aug 11, 2013
    USA New Congressional effort launched to enlist Sikhs in US Army Breaking News Jul 19, 2013
    USA U.S. Congress launches American Sikh Congressional Caucus Breaking News Apr 25, 2013

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    It depends in what way you want to 'open' the table. Having returned a
    reference to the external database with the OpenDatabase methods you can
    return a reference to any of its tables via its TableDefs collection. You
    can then establish a recordsdet using the OpenRecordset method and then
    iterate through the recordset in code, search for a row in the recordset etc.

    If, however, you simply want to open the table in datasheet view then this
    won't do that. You could create a link to the external table in code using
    the TransferDatabase method and then open the linked table. Another method
    would be to create a temporary query with an IN clause which references the
    external database and open that, e.g.

    Dim dbs As DAO.Database, qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strPath As String

    strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"

    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("Temp", strSQL)

    DoCmd.OpenQuery "Temp"

    dbs.QueryDefs.Delete "Temp"

    Note that the path to the external file needs to be in quotes in the SQL
    statement so when concatenating the strPath variable into the string
    expression doubled quotes are used as these when used within a string
    delimited by quotes evaluate to a quotes character.

    Ken Sheridan
    Stafford, England

    "RB Smissaert" wrote:

    > Given I created/updated a .mdb file like with:
    >
    > Dim db As DAO.Database
    >
    > Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    > or
    > Set db = DBEngine.OpenDatabase(strMDBPath)
    >
    > How do I launch this .mdb file and open a specified table?
    > I could do it with Shell I suppose (not sure about the table though), but
    > given
    > the above code there might be a better way.
    >
    > RBS
    >
    >
     
  4. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Just want to do the as I would do manually:
    double-click the .mdb
    double-click the table, so it open in data view.

    Tried your code, but get:
    Object variable or With block variable not set (Error 91)
    at the line:
    Set qdf = dbs.CreateQueryDef("Temp", strSQL)

    Can't I just automate Access and open the table with the Access methods?
    Not sure why I have to run SQL if the table has been made already.
    I am new to Access, so maybe I overlook some fundamental things.

    RBS

    "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    news:39439491-A370-4683-9EBD-BA783D2615E9@microsoft.com...
    > It depends in what way you want to 'open' the table. Having returned a
    > reference to the external database with the OpenDatabase methods you can
    > return a reference to any of its tables via its TableDefs collection. You
    > can then establish a recordsdet using the OpenRecordset method and then
    > iterate through the recordset in code, search for a row in the recordset
    > etc.
    >
    > If, however, you simply want to open the table in datasheet view then this
    > won't do that. You could create a link to the external table in code
    > using
    > the TransferDatabase method and then open the linked table. Another
    > method
    > would be to create a temporary query with an IN clause which references
    > the
    > external database and open that, e.g.
    >
    > Dim dbs As DAO.Database, qdf As DAO.QueryDef
    > Dim strSQL As String
    > Dim strPath As String
    >
    > strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    > strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"
    >
    > Set dbs = CurrentDb
    > Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >
    > DoCmd.OpenQuery "Temp"
    >
    > dbs.QueryDefs.Delete "Temp"
    >
    > Note that the path to the external file needs to be in quotes in the SQL
    > statement so when concatenating the strPath variable into the string
    > expression doubled quotes are used as these when used within a string
    > delimited by quotes evaluate to a quotes character.
    >
    > Ken Sheridan
    > Stafford, England
    >
    > "RB Smissaert" wrote:
    >
    >> Given I created/updated a .mdb file like with:
    >>
    >> Dim db As DAO.Database
    >>
    >> Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    >> or
    >> Set db = DBEngine.OpenDatabase(strMDBPath)
    >>
    >> How do I launch this .mdb file and open a specified table?
    >> I could do it with Shell I suppose (not sure about the table though), but
    >> given
    >> the above code there might be a better way.
    >>
    >> RBS
    >>
    >>

    >
     
  5. MH

    MH
    Expand Collapse
    Guest

    You may be going about this whole thing the wrong way, usually a database
    (your .mdb file) is set up in advance with all the tables, queries, forms,
    reports and code it needs to do the job already created. Could you give
    some kind of overall view of what you're trying to achieve? Maybe someone
    can come up with the "ideal" solution for you (obviously we'd leave all the
    difficult stuff for you to figure out though!)

    Regards

    MH

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23vpblZFfGHA.2172@TK2MSFTNGP04.phx.gbl...
    > Just want to do the as I would do manually:
    > double-click the .mdb
    > double-click the table, so it open in data view.
    >
    > Tried your code, but get:
    > Object variable or With block variable not set (Error 91)
    > at the line:
    > Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >
    > Can't I just automate Access and open the table with the Access methods?
    > Not sure why I have to run SQL if the table has been made already.
    > I am new to Access, so maybe I overlook some fundamental things.
    >
    > RBS
    >
    > "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    > news:39439491-A370-4683-9EBD-BA783D2615E9@microsoft.com...
    >> It depends in what way you want to 'open' the table. Having returned a
    >> reference to the external database with the OpenDatabase methods you can
    >> return a reference to any of its tables via its TableDefs collection.
    >> You
    >> can then establish a recordsdet using the OpenRecordset method and then
    >> iterate through the recordset in code, search for a row in the recordset
    >> etc.
    >>
    >> If, however, you simply want to open the table in datasheet view then
    >> this
    >> won't do that. You could create a link to the external table in code
    >> using
    >> the TransferDatabase method and then open the linked table. Another
    >> method
    >> would be to create a temporary query with an IN clause which references
    >> the
    >> external database and open that, e.g.
    >>
    >> Dim dbs As DAO.Database, qdf As DAO.QueryDef
    >> Dim strSQL As String
    >> Dim strPath As String
    >>
    >> strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    >> strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"
    >>
    >> Set dbs = CurrentDb
    >> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>
    >> DoCmd.OpenQuery "Temp"
    >>
    >> dbs.QueryDefs.Delete "Temp"
    >>
    >> Note that the path to the external file needs to be in quotes in the SQL
    >> statement so when concatenating the strPath variable into the string
    >> expression doubled quotes are used as these when used within a string
    >> delimited by quotes evaluate to a quotes character.
    >>
    >> Ken Sheridan
    >> Stafford, England
    >>
    >> "RB Smissaert" wrote:
    >>
    >>> Given I created/updated a .mdb file like with:
    >>>
    >>> Dim db As DAO.Database
    >>>
    >>> Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    >>> or
    >>> Set db = DBEngine.OpenDatabase(strMDBPath)
    >>>
    >>> How do I launch this .mdb file and open a specified table?
    >>> I could do it with Shell I suppose (not sure about the table though),
    >>> but
    >>> given
    >>> the above code there might be a better way.
    >>>
    >>> RBS
    >>>
    >>>

    >>

    >
     
  6. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    OK, let me explain.
    This is a database frontend, based on an Excel .xla add-in.
    It connects to an Interbase database via ODBC.
    The great majority of the reporting will be done in Excel,
    but there is the option to output to Access.
    So, the user runs a report from the .xla form and opts to output to an
    Access .mdb file.
    When the report is finished it is nice to directly launch that .mdb and also
    open the
    newly created table.
    Now I can do the .mdb opening with the ShellExecute API and a simple
    AppActivate.
    It won't open the table though and that is the bit left to be done.
    Hope this makes it all clear.

    RBS


    "MH" <noway@nohow.co.uk> wrote in message
    news:OECFvJGfGHA.2188@TK2MSFTNGP05.phx.gbl...
    > You may be going about this whole thing the wrong way, usually a database
    > (your .mdb file) is set up in advance with all the tables, queries, forms,
    > reports and code it needs to do the job already created. Could you give
    > some kind of overall view of what you're trying to achieve? Maybe someone
    > can come up with the "ideal" solution for you (obviously we'd leave all
    > the difficult stuff for you to figure out though!)
    >
    > Regards
    >
    > MH
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23vpblZFfGHA.2172@TK2MSFTNGP04.phx.gbl...
    >> Just want to do the as I would do manually:
    >> double-click the .mdb
    >> double-click the table, so it open in data view.
    >>
    >> Tried your code, but get:
    >> Object variable or With block variable not set (Error 91)
    >> at the line:
    >> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>
    >> Can't I just automate Access and open the table with the Access methods?
    >> Not sure why I have to run SQL if the table has been made already.
    >> I am new to Access, so maybe I overlook some fundamental things.
    >>
    >> RBS
    >>
    >> "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    >> news:39439491-A370-4683-9EBD-BA783D2615E9@microsoft.com...
    >>> It depends in what way you want to 'open' the table. Having returned a
    >>> reference to the external database with the OpenDatabase methods you can
    >>> return a reference to any of its tables via its TableDefs collection.
    >>> You
    >>> can then establish a recordsdet using the OpenRecordset method and then
    >>> iterate through the recordset in code, search for a row in the recordset
    >>> etc.
    >>>
    >>> If, however, you simply want to open the table in datasheet view then
    >>> this
    >>> won't do that. You could create a link to the external table in code
    >>> using
    >>> the TransferDatabase method and then open the linked table. Another
    >>> method
    >>> would be to create a temporary query with an IN clause which references
    >>> the
    >>> external database and open that, e.g.
    >>>
    >>> Dim dbs As DAO.Database, qdf As DAO.QueryDef
    >>> Dim strSQL As String
    >>> Dim strPath As String
    >>>
    >>> strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    >>> strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"
    >>>
    >>> Set dbs = CurrentDb
    >>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>
    >>> DoCmd.OpenQuery "Temp"
    >>>
    >>> dbs.QueryDefs.Delete "Temp"
    >>>
    >>> Note that the path to the external file needs to be in quotes in the SQL
    >>> statement so when concatenating the strPath variable into the string
    >>> expression doubled quotes are used as these when used within a string
    >>> delimited by quotes evaluate to a quotes character.
    >>>
    >>> Ken Sheridan
    >>> Stafford, England
    >>>
    >>> "RB Smissaert" wrote:
    >>>
    >>>> Given I created/updated a .mdb file like with:
    >>>>
    >>>> Dim db As DAO.Database
    >>>>
    >>>> Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    >>>> or
    >>>> Set db = DBEngine.OpenDatabase(strMDBPath)
    >>>>
    >>>> How do I launch this .mdb file and open a specified table?
    >>>> I could do it with Shell I suppose (not sure about the table though),
    >>>> but
    >>>> given
    >>>> the above code there might be a better way.
    >>>>
    >>>> RBS
    >>>>
    >>>>
    >>>

    >>

    >
    >
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    You shouldn't be opening tables anyhow. Create a form that displays the
    data, and set that form as the Startup form (under Tools | Startup)

    If you can't (or won't) do that, you can create a macro named AutoExec
    that'll open the table.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl...
    > OK, let me explain.
    > This is a database frontend, based on an Excel .xla add-in.
    > It connects to an Interbase database via ODBC.
    > The great majority of the reporting will be done in Excel,
    > but there is the option to output to Access.
    > So, the user runs a report from the .xla form and opts to output to an
    > Access .mdb file.
    > When the report is finished it is nice to directly launch that .mdb and
    > also open the
    > newly created table.
    > Now I can do the .mdb opening with the ShellExecute API and a simple
    > AppActivate.
    > It won't open the table though and that is the bit left to be done.
    > Hope this makes it all clear.
    >
    > RBS
    >
    >
    > "MH" <noway@nohow.co.uk> wrote in message
    > news:OECFvJGfGHA.2188@TK2MSFTNGP05.phx.gbl...
    >> You may be going about this whole thing the wrong way, usually a database
    >> (your .mdb file) is set up in advance with all the tables, queries,
    >> forms, reports and code it needs to do the job already created. Could
    >> you give some kind of overall view of what you're trying to achieve?
    >> Maybe someone can come up with the "ideal" solution for you (obviously
    >> we'd leave all the difficult stuff for you to figure out though!)
    >>
    >> Regards
    >>
    >> MH
    >>
    >> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:%23vpblZFfGHA.2172@TK2MSFTNGP04.phx.gbl...
    >>> Just want to do the as I would do manually:
    >>> double-click the .mdb
    >>> double-click the table, so it open in data view.
    >>>
    >>> Tried your code, but get:
    >>> Object variable or With block variable not set (Error 91)
    >>> at the line:
    >>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>
    >>> Can't I just automate Access and open the table with the Access methods?
    >>> Not sure why I have to run SQL if the table has been made already.
    >>> I am new to Access, so maybe I overlook some fundamental things.
    >>>
    >>> RBS
    >>>
    >>> "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    >>> news:39439491-A370-4683-9EBD-BA783D2615E9@microsoft.com...
    >>>> It depends in what way you want to 'open' the table. Having returned a
    >>>> reference to the external database with the OpenDatabase methods you
    >>>> can
    >>>> return a reference to any of its tables via its TableDefs collection.
    >>>> You
    >>>> can then establish a recordsdet using the OpenRecordset method and then
    >>>> iterate through the recordset in code, search for a row in the
    >>>> recordset etc.
    >>>>
    >>>> If, however, you simply want to open the table in datasheet view then
    >>>> this
    >>>> won't do that. You could create a link to the external table in code
    >>>> using
    >>>> the TransferDatabase method and then open the linked table. Another
    >>>> method
    >>>> would be to create a temporary query with an IN clause which references
    >>>> the
    >>>> external database and open that, e.g.
    >>>>
    >>>> Dim dbs As DAO.Database, qdf As DAO.QueryDef
    >>>> Dim strSQL As String
    >>>> Dim strPath As String
    >>>>
    >>>> strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    >>>> strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"
    >>>>
    >>>> Set dbs = CurrentDb
    >>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>
    >>>> DoCmd.OpenQuery "Temp"
    >>>>
    >>>> dbs.QueryDefs.Delete "Temp"
    >>>>
    >>>> Note that the path to the external file needs to be in quotes in the
    >>>> SQL
    >>>> statement so when concatenating the strPath variable into the string
    >>>> expression doubled quotes are used as these when used within a string
    >>>> delimited by quotes evaluate to a quotes character.
    >>>>
    >>>> Ken Sheridan
    >>>> Stafford, England
    >>>>
    >>>> "RB Smissaert" wrote:
    >>>>
    >>>>> Given I created/updated a .mdb file like with:
    >>>>>
    >>>>> Dim db As DAO.Database
    >>>>>
    >>>>> Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    >>>>> or
    >>>>> Set db = DBEngine.OpenDatabase(strMDBPath)
    >>>>>
    >>>>> How do I launch this .mdb file and open a specified table?
    >>>>> I could do it with Shell I suppose (not sure about the table though),
    >>>>> but
    >>>>> given
    >>>>> the above code there might be a better way.
    >>>>>
    >>>>> RBS
    >>>>>
    >>>>>
    >>>>
    >>>

    >>
    >>

    >
     
  8. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl
    > OK, let me explain.
    > This is a database frontend, based on an Excel .xla add-in.
    > It connects to an Interbase database via ODBC.
    > The great majority of the reporting will be done in Excel,
    > but there is the option to output to Access.
    > So, the user runs a report from the .xla form and opts to output to an
    > Access .mdb file.
    > When the report is finished it is nice to directly launch that .mdb
    > and also open the
    > newly created table.
    > Now I can do the .mdb opening with the ShellExecute API and a simple
    > AppActivate.
    > It won't open the table though and that is the bit left to be done.
    > Hope this makes it all clear.


    You can probably do it like this:

    Dim appAccess As Object
    Dim strDatabasePath As String
    Dim strTableName As String

    strDatabasePath = "C:\Your Path\YourDB.mdb"
    strTableName = "YourTable"

    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strDatabasePath
    appAccess.Visible = True
    appAccess.DoCmd.OpenTable strTableName
    Set appAccess = Nothing


    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  9. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Not sure what is wrong with opening a table.
    All the users will want to do is see the data and maybe sort and/or filter.
    These are newly made .mdb files as the result of a SQL query run from Excel.
    So, if there has to be a form I will have to add extra code to create this
    form everytime.
    Still, I will have a look into this suggestion.

    RBS

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23Ld3%23JIfGHA.3792@TK2MSFTNGP03.phx.gbl...
    > You shouldn't be opening tables anyhow. Create a form that displays the
    > data, and set that form as the Startup form (under Tools | Startup)
    >
    > If you can't (or won't) do that, you can create a macro named AutoExec
    > that'll open the table.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl...
    >> OK, let me explain.
    >> This is a database frontend, based on an Excel .xla add-in.
    >> It connects to an Interbase database via ODBC.
    >> The great majority of the reporting will be done in Excel,
    >> but there is the option to output to Access.
    >> So, the user runs a report from the .xla form and opts to output to an
    >> Access .mdb file.
    >> When the report is finished it is nice to directly launch that .mdb and
    >> also open the
    >> newly created table.
    >> Now I can do the .mdb opening with the ShellExecute API and a simple
    >> AppActivate.
    >> It won't open the table though and that is the bit left to be done.
    >> Hope this makes it all clear.
    >>
    >> RBS
    >>
    >>
    >> "MH" <noway@nohow.co.uk> wrote in message
    >> news:OECFvJGfGHA.2188@TK2MSFTNGP05.phx.gbl...
    >>> You may be going about this whole thing the wrong way, usually a
    >>> database (your .mdb file) is set up in advance with all the tables,
    >>> queries, forms, reports and code it needs to do the job already created.
    >>> Could you give some kind of overall view of what you're trying to
    >>> achieve? Maybe someone can come up with the "ideal" solution for you
    >>> (obviously we'd leave all the difficult stuff for you to figure out
    >>> though!)
    >>>
    >>> Regards
    >>>
    >>> MH
    >>>
    >>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:%23vpblZFfGHA.2172@TK2MSFTNGP04.phx.gbl...
    >>>> Just want to do the as I would do manually:
    >>>> double-click the .mdb
    >>>> double-click the table, so it open in data view.
    >>>>
    >>>> Tried your code, but get:
    >>>> Object variable or With block variable not set (Error 91)
    >>>> at the line:
    >>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>
    >>>> Can't I just automate Access and open the table with the Access
    >>>> methods?
    >>>> Not sure why I have to run SQL if the table has been made already.
    >>>> I am new to Access, so maybe I overlook some fundamental things.
    >>>>
    >>>> RBS
    >>>>
    >>>> "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
    >>>> news:39439491-A370-4683-9EBD-BA783D2615E9@microsoft.com...
    >>>>> It depends in what way you want to 'open' the table. Having returned
    >>>>> a
    >>>>> reference to the external database with the OpenDatabase methods you
    >>>>> can
    >>>>> return a reference to any of its tables via its TableDefs collection.
    >>>>> You
    >>>>> can then establish a recordsdet using the OpenRecordset method and
    >>>>> then
    >>>>> iterate through the recordset in code, search for a row in the
    >>>>> recordset etc.
    >>>>>
    >>>>> If, however, you simply want to open the table in datasheet view then
    >>>>> this
    >>>>> won't do that. You could create a link to the external table in code
    >>>>> using
    >>>>> the TransferDatabase method and then open the linked table. Another
    >>>>> method
    >>>>> would be to create a temporary query with an IN clause which
    >>>>> references the
    >>>>> external database and open that, e.g.
    >>>>>
    >>>>> Dim dbs As DAO.Database, qdf As DAO.QueryDef
    >>>>> Dim strSQL As String
    >>>>> Dim strPath As String
    >>>>>
    >>>>> strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    >>>>> strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"
    >>>>>
    >>>>> Set dbs = CurrentDb
    >>>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>>
    >>>>> DoCmd.OpenQuery "Temp"
    >>>>>
    >>>>> dbs.QueryDefs.Delete "Temp"
    >>>>>
    >>>>> Note that the path to the external file needs to be in quotes in the
    >>>>> SQL
    >>>>> statement so when concatenating the strPath variable into the string
    >>>>> expression doubled quotes are used as these when used within a string
    >>>>> delimited by quotes evaluate to a quotes character.
    >>>>>
    >>>>> Ken Sheridan
    >>>>> Stafford, England
    >>>>>
    >>>>> "RB Smissaert" wrote:
    >>>>>
    >>>>>> Given I created/updated a .mdb file like with:
    >>>>>>
    >>>>>> Dim db As DAO.Database
    >>>>>>
    >>>>>> Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    >>>>>> or
    >>>>>> Set db = DBEngine.OpenDatabase(strMDBPath)
    >>>>>>
    >>>>>> How do I launch this .mdb file and open a specified table?
    >>>>>> I could do it with Shell I suppose (not sure about the table though),
    >>>>>> but
    >>>>>> given
    >>>>>> the above code there might be a better way.
    >>>>>>
    >>>>>> RBS
    >>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>
    >>>

    >>

    >
    >
     
  10. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Thanks, that works.
    Had tried it already, but for some reason it didn't work.
    Must have done something slightly different.
    This does the trick.

    RBS

    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:Ou4ZEUIfGHA.5104@TK2MSFTNGP04.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl
    >> OK, let me explain.
    >> This is a database frontend, based on an Excel .xla add-in.
    >> It connects to an Interbase database via ODBC.
    >> The great majority of the reporting will be done in Excel,
    >> but there is the option to output to Access.
    >> So, the user runs a report from the .xla form and opts to output to an
    >> Access .mdb file.
    >> When the report is finished it is nice to directly launch that .mdb
    >> and also open the
    >> newly created table.
    >> Now I can do the .mdb opening with the ShellExecute API and a simple
    >> AppActivate.
    >> It won't open the table though and that is the bit left to be done.
    >> Hope this makes it all clear.

    >
    > You can probably do it like this:
    >
    > Dim appAccess As Object
    > Dim strDatabasePath As String
    > Dim strTableName As String
    >
    > strDatabasePath = "C:\Your Path\YourDB.mdb"
    > strTableName = "YourTable"
    >
    > Set appAccess = CreateObject("Access.Application")
    > appAccess.OpenCurrentDatabase strDatabasePath
    > appAccess.Visible = True
    > appAccess.DoCmd.OpenTable strTableName
    > Set appAccess = Nothing
    >
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  11. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Tables are meant for storing data, not for displaying the data, and
    certainly not for updating the data.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:eMgenhKfGHA.1264@TK2MSFTNGP05.phx.gbl...
    > Not sure what is wrong with opening a table.
    > All the users will want to do is see the data and maybe sort and/or
    > filter.
    > These are newly made .mdb files as the result of a SQL query run from
    > Excel.
    > So, if there has to be a form I will have to add extra code to create this
    > form everytime.
    > Still, I will have a look into this suggestion.
    >
    > RBS
    >
    > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:%23Ld3%23JIfGHA.3792@TK2MSFTNGP03.phx.gbl...
    >> You shouldn't be opening tables anyhow. Create a form that displays the
    >> data, and set that form as the Startup form (under Tools | Startup)
    >>
    >> If you can't (or won't) do that, you can create a macro named AutoExec
    >> that'll open the table.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no private e-mails, please)
    >>
    >>
    >> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl...
    >>> OK, let me explain.
    >>> This is a database frontend, based on an Excel .xla add-in.
    >>> It connects to an Interbase database via ODBC.
    >>> The great majority of the reporting will be done in Excel,
    >>> but there is the option to output to Access.
    >>> So, the user runs a report from the .xla form and opts to output to an
    >>> Access .mdb file.
    >>> When the report is finished it is nice to directly launch that .mdb and
    >>> also open the
    >>> newly created table.
    >>> Now I can do the .mdb opening with the ShellExecute API and a simple
    >>> AppActivate.
    >>> It won't open the table though and that is the bit left to be done.
    >>> Hope this makes it all clear.
    >>>
    >>> RBS
    >>>
    >>>
    >>> "MH" <noway@nohow.co.uk> wrote in message
    >>> news:OECFvJGfGHA.2188@TK2MSFTNGP05.phx.gbl...
    >>>> You may be going about this whole thing the wrong way, usually a
    >>>> database (your .mdb file) is set up in advance with all the tables,
    >>>> queries, forms, reports and code it needs to do the job already
    >>>> created. Could you give some kind of overall view of what you're trying
    >>>> to achieve? Maybe someone can come up with the "ideal" solution for you
    >>>> (obviously we'd leave all the difficult stuff for you to figure out
    >>>> though!)
    >>>>
    >>>> Regards
    >>>>
    >>>> MH
    >>>>
    >>>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>> news:%23vpblZFfGHA.2172@TK2MSFTNGP04.phx.gbl...
    >>>>> Just want to do the as I would do manually:
    >>>>> double-click the .mdb
    >>>>> double-click the table, so it open in data view.
    >>>>>
    >>>>> Tried your code, but get:
    >>>>> Object variable or With block variable not set (Error 91)
    >>>>> at the line:
    >>>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>>
    >>>>> Can't I just automate Access and open the table with the Access
    >>>>> methods?
    >>>>> Not sure why I have to run SQL if the table has been made already.
    >>>>> I am new to Access, so maybe I overlook some fundamental things.
    >>>>>
    >>>>> RBS
    >>>>>
    >>>>> "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in
    >>>>> message news:39439491-A370-4683-9EBD-BA783D2615E9@microsoft.com...
    >>>>>> It depends in what way you want to 'open' the table. Having returned
    >>>>>> a
    >>>>>> reference to the external database with the OpenDatabase methods you
    >>>>>> can
    >>>>>> return a reference to any of its tables via its TableDefs collection.
    >>>>>> You
    >>>>>> can then establish a recordsdet using the OpenRecordset method and
    >>>>>> then
    >>>>>> iterate through the recordset in code, search for a row in the
    >>>>>> recordset etc.
    >>>>>>
    >>>>>> If, however, you simply want to open the table in datasheet view then
    >>>>>> this
    >>>>>> won't do that. You could create a link to the external table in code
    >>>>>> using
    >>>>>> the TransferDatabase method and then open the linked table. Another
    >>>>>> method
    >>>>>> would be to create a temporary query with an IN clause which
    >>>>>> references the
    >>>>>> external database and open that, e.g.
    >>>>>>
    >>>>>> Dim dbs As DAO.Database, qdf As DAO.QueryDef
    >>>>>> Dim strSQL As String
    >>>>>> Dim strPath As String
    >>>>>>
    >>>>>> strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    >>>>>> strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"
    >>>>>>
    >>>>>> Set dbs = CurrentDb
    >>>>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>>>
    >>>>>> DoCmd.OpenQuery "Temp"
    >>>>>>
    >>>>>> dbs.QueryDefs.Delete "Temp"
    >>>>>>
    >>>>>> Note that the path to the external file needs to be in quotes in the
    >>>>>> SQL
    >>>>>> statement so when concatenating the strPath variable into the string
    >>>>>> expression doubled quotes are used as these when used within a string
    >>>>>> delimited by quotes evaluate to a quotes character.
    >>>>>>
    >>>>>> Ken Sheridan
    >>>>>> Stafford, England
    >>>>>>
    >>>>>> "RB Smissaert" wrote:
    >>>>>>
    >>>>>>> Given I created/updated a .mdb file like with:
    >>>>>>>
    >>>>>>> Dim db As DAO.Database
    >>>>>>>
    >>>>>>> Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    >>>>>>> or
    >>>>>>> Set db = DBEngine.OpenDatabase(strMDBPath)
    >>>>>>>
    >>>>>>> How do I launch this .mdb file and open a specified table?
    >>>>>>> I could do it with Shell I suppose (not sure about the table
    >>>>>>> though), but
    >>>>>>> given
    >>>>>>> the above code there might be a better way.
    >>>>>>>
    >>>>>>> RBS
    >>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>>
    >>>

    >>
    >>

    >
     
  12. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Sure, I understand what you are saying, but this is just
    for a quick display. It is not meant to be a worked-out application.
    Updating the data won't happen. The users of this won't probably
    know how to do it.
    Sole purpose of the whole thing is to see data that can't be shown in
    Excel due to having more than 65535 rows.

    Considering all of the above, what would be the advantage in showing
    the data in a form?

    RBS


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:unyhDIMfGHA.5088@TK2MSFTNGP02.phx.gbl...
    > Tables are meant for storing data, not for displaying the data, and
    > certainly not for updating the data.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:eMgenhKfGHA.1264@TK2MSFTNGP05.phx.gbl...
    >> Not sure what is wrong with opening a table.
    >> All the users will want to do is see the data and maybe sort and/or
    >> filter.
    >> These are newly made .mdb files as the result of a SQL query run from
    >> Excel.
    >> So, if there has to be a form I will have to add extra code to create
    >> this form everytime.
    >> Still, I will have a look into this suggestion.
    >>
    >> RBS
    >>
    >> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    >> news:%23Ld3%23JIfGHA.3792@TK2MSFTNGP03.phx.gbl...
    >>> You shouldn't be opening tables anyhow. Create a form that displays the
    >>> data, and set that form as the Startup form (under Tools | Startup)
    >>>
    >>> If you can't (or won't) do that, you can create a macro named AutoExec
    >>> that'll open the table.
    >>>
    >>> --
    >>> Doug Steele, Microsoft Access MVP
    >>> http://I.Am/DougSteele
    >>> (no private e-mails, please)
    >>>
    >>>
    >>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl...
    >>>> OK, let me explain.
    >>>> This is a database frontend, based on an Excel .xla add-in.
    >>>> It connects to an Interbase database via ODBC.
    >>>> The great majority of the reporting will be done in Excel,
    >>>> but there is the option to output to Access.
    >>>> So, the user runs a report from the .xla form and opts to output to an
    >>>> Access .mdb file.
    >>>> When the report is finished it is nice to directly launch that .mdb and
    >>>> also open the
    >>>> newly created table.
    >>>> Now I can do the .mdb opening with the ShellExecute API and a simple
    >>>> AppActivate.
    >>>> It won't open the table though and that is the bit left to be done.
    >>>> Hope this makes it all clear.
    >>>>
    >>>> RBS
    >>>>
    >>>>
    >>>> "MH" <noway@nohow.co.uk> wrote in message
    >>>> news:OECFvJGfGHA.2188@TK2MSFTNGP05.phx.gbl...
    >>>>> You may be going about this whole thing the wrong way, usually a
    >>>>> database (your .mdb file) is set up in advance with all the tables,
    >>>>> queries, forms, reports and code it needs to do the job already
    >>>>> created. Could you give some kind of overall view of what you're
    >>>>> trying to achieve? Maybe someone can come up with the "ideal" solution
    >>>>> for you (obviously we'd leave all the difficult stuff for you to
    >>>>> figure out though!)
    >>>>>
    >>>>> Regards
    >>>>>
    >>>>> MH
    >>>>>
    >>>>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>>> news:%23vpblZFfGHA.2172@TK2MSFTNGP04.phx.gbl...
    >>>>>> Just want to do the as I would do manually:
    >>>>>> double-click the .mdb
    >>>>>> double-click the table, so it open in data view.
    >>>>>>
    >>>>>> Tried your code, but get:
    >>>>>> Object variable or With block variable not set (Error 91)
    >>>>>> at the line:
    >>>>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>>>
    >>>>>> Can't I just automate Access and open the table with the Access
    >>>>>> methods?
    >>>>>> Not sure why I have to run SQL if the table has been made already.
    >>>>>> I am new to Access, so maybe I overlook some fundamental things.
    >>>>>>
    >>>>>> RBS
    >>>>>>
    >>>>>> "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in
    >>>>>> message news:39439491-A370-4683-9EBD-BA783D2615E9@microsoft.com...
    >>>>>>> It depends in what way you want to 'open' the table. Having
    >>>>>>> returned a
    >>>>>>> reference to the external database with the OpenDatabase methods you
    >>>>>>> can
    >>>>>>> return a reference to any of its tables via its TableDefs
    >>>>>>> collection. You
    >>>>>>> can then establish a recordsdet using the OpenRecordset method and
    >>>>>>> then
    >>>>>>> iterate through the recordset in code, search for a row in the
    >>>>>>> recordset etc.
    >>>>>>>
    >>>>>>> If, however, you simply want to open the table in datasheet view
    >>>>>>> then this
    >>>>>>> won't do that. You could create a link to the external table in
    >>>>>>> code using
    >>>>>>> the TransferDatabase method and then open the linked table. Another
    >>>>>>> method
    >>>>>>> would be to create a temporary query with an IN clause which
    >>>>>>> references the
    >>>>>>> external database and open that, e.g.
    >>>>>>>
    >>>>>>> Dim dbs As DAO.Database, qdf As DAO.QueryDef
    >>>>>>> Dim strSQL As String
    >>>>>>> Dim strPath As String
    >>>>>>>
    >>>>>>> strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    >>>>>>> strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"
    >>>>>>>
    >>>>>>> Set dbs = CurrentDb
    >>>>>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>>>>
    >>>>>>> DoCmd.OpenQuery "Temp"
    >>>>>>>
    >>>>>>> dbs.QueryDefs.Delete "Temp"
    >>>>>>>
    >>>>>>> Note that the path to the external file needs to be in quotes in the
    >>>>>>> SQL
    >>>>>>> statement so when concatenating the strPath variable into the string
    >>>>>>> expression doubled quotes are used as these when used within a
    >>>>>>> string
    >>>>>>> delimited by quotes evaluate to a quotes character.
    >>>>>>>
    >>>>>>> Ken Sheridan
    >>>>>>> Stafford, England
    >>>>>>>
    >>>>>>> "RB Smissaert" wrote:
    >>>>>>>
    >>>>>>>> Given I created/updated a .mdb file like with:
    >>>>>>>>
    >>>>>>>> Dim db As DAO.Database
    >>>>>>>>
    >>>>>>>> Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    >>>>>>>> or
    >>>>>>>> Set db = DBEngine.OpenDatabase(strMDBPath)
    >>>>>>>>
    >>>>>>>> How do I launch this .mdb file and open a specified table?
    >>>>>>>> I could do it with Shell I suppose (not sure about the table
    >>>>>>>> though), but
    >>>>>>>> given
    >>>>>>>> the above code there might be a better way.
    >>>>>>>>
    >>>>>>>> RBS
    >>>>>>>>
    >>>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>>
    >>>>
    >>>
    >>>

    >>

    >
    >
     
  13. MH

    MH
    Expand Collapse
    Guest

    Glad you finally got it all sorted out RB.

    One option you may want to give your users is to select an existing MDB file
    and the table name to export to, it may be better to store the output from
    different queries in one mdb file. Just a suggestion of course.

    MH

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:OnOWGlKfGHA.2076@TK2MSFTNGP04.phx.gbl...
    > Thanks, that works.
    > Had tried it already, but for some reason it didn't work.
    > Must have done something slightly different.
    > This does the trick.
    >
    > RBS
    >
    > "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    > news:Ou4ZEUIfGHA.5104@TK2MSFTNGP04.phx.gbl...
    >> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl
    >>> OK, let me explain.
    >>> This is a database frontend, based on an Excel .xla add-in.
    >>> It connects to an Interbase database via ODBC.
    >>> The great majority of the reporting will be done in Excel,
    >>> but there is the option to output to Access.
    >>> So, the user runs a report from the .xla form and opts to output to an
    >>> Access .mdb file.
    >>> When the report is finished it is nice to directly launch that .mdb
    >>> and also open the
    >>> newly created table.
    >>> Now I can do the .mdb opening with the ShellExecute API and a simple
    >>> AppActivate.
    >>> It won't open the table though and that is the bit left to be done.
    >>> Hope this makes it all clear.

    >>
    >> You can probably do it like this:
    >>
    >> Dim appAccess As Object
    >> Dim strDatabasePath As String
    >> Dim strTableName As String
    >>
    >> strDatabasePath = "C:\Your Path\YourDB.mdb"
    >> strTableName = "YourTable"
    >>
    >> Set appAccess = CreateObject("Access.Application")
    >> appAccess.OpenCurrentDatabase strDatabasePath
    >> appAccess.Visible = True
    >> appAccess.DoCmd.OpenTable strTableName
    >> Set appAccess = Nothing
    >>
    >>
    >> --
    >> Dirk Goldgar, MS Access MVP
    >> www.datagnostics.com
    >>
    >> (please reply to the newsgroup)
    >>
    >>

    >
     
  14. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    That is exactly what I am doing already.
    Option to make new or use existing .mdb.
    Option to name the table.
    All working nicely.

    All this is just a little side issue to have a go with Access and offer the
    users some other option.
    The main thing will be to re-write a large part of my application and do all
    the data manipulations
    in Access, rather than arrays and SQL on text files. All doable, although I
    can see it will be a big job.

    RBS


    "MH" <noway@nohow.co.uk> wrote in message
    news:%23RkEnvMfGHA.2188@TK2MSFTNGP05.phx.gbl...
    > Glad you finally got it all sorted out RB.
    >
    > One option you may want to give your users is to select an existing MDB
    > file and the table name to export to, it may be better to store the output
    > from different queries in one mdb file. Just a suggestion of course.
    >
    > MH
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:OnOWGlKfGHA.2076@TK2MSFTNGP04.phx.gbl...
    >> Thanks, that works.
    >> Had tried it already, but for some reason it didn't work.
    >> Must have done something slightly different.
    >> This does the trick.
    >>
    >> RBS
    >>
    >> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    >> news:Ou4ZEUIfGHA.5104@TK2MSFTNGP04.phx.gbl...
    >>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl
    >>>> OK, let me explain.
    >>>> This is a database frontend, based on an Excel .xla add-in.
    >>>> It connects to an Interbase database via ODBC.
    >>>> The great majority of the reporting will be done in Excel,
    >>>> but there is the option to output to Access.
    >>>> So, the user runs a report from the .xla form and opts to output to an
    >>>> Access .mdb file.
    >>>> When the report is finished it is nice to directly launch that .mdb
    >>>> and also open the
    >>>> newly created table.
    >>>> Now I can do the .mdb opening with the ShellExecute API and a simple
    >>>> AppActivate.
    >>>> It won't open the table though and that is the bit left to be done.
    >>>> Hope this makes it all clear.
    >>>
    >>> You can probably do it like this:
    >>>
    >>> Dim appAccess As Object
    >>> Dim strDatabasePath As String
    >>> Dim strTableName As String
    >>>
    >>> strDatabasePath = "C:\Your Path\YourDB.mdb"
    >>> strTableName = "YourTable"
    >>>
    >>> Set appAccess = CreateObject("Access.Application")
    >>> appAccess.OpenCurrentDatabase strDatabasePath
    >>> appAccess.Visible = True
    >>> appAccess.DoCmd.OpenTable strTableName
    >>> Set appAccess = Nothing
    >>>
    >>>
    >>> --
    >>> Dirk Goldgar, MS Access MVP
    >>> www.datagnostics.com
    >>>
    >>> (please reply to the newsgroup)
    >>>
    >>>

    >>

    >
    >
     
  15. MH

    MH
    Expand Collapse
    Guest

    I'm sure you'll cope! <g>

    MH

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:eyoe2fNfGHA.2416@TK2MSFTNGP03.phx.gbl...
    > That is exactly what I am doing already.
    > Option to make new or use existing .mdb.
    > Option to name the table.
    > All working nicely.
    >
    > All this is just a little side issue to have a go with Access and offer
    > the users some other option.
    > The main thing will be to re-write a large part of my application and do
    > all the data manipulations
    > in Access, rather than arrays and SQL on text files. All doable, although
    > I can see it will be a big job.
    >
    > RBS
    >
    >
    > "MH" <noway@nohow.co.uk> wrote in message
    > news:%23RkEnvMfGHA.2188@TK2MSFTNGP05.phx.gbl...
    >> Glad you finally got it all sorted out RB.
    >>
    >> One option you may want to give your users is to select an existing MDB
    >> file and the table name to export to, it may be better to store the
    >> output from different queries in one mdb file. Just a suggestion of
    >> course.
    >>
    >> MH
    >>
    >> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:OnOWGlKfGHA.2076@TK2MSFTNGP04.phx.gbl...
    >>> Thanks, that works.
    >>> Had tried it already, but for some reason it didn't work.
    >>> Must have done something slightly different.
    >>> This does the trick.
    >>>
    >>> RBS
    >>>
    >>> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    >>> news:Ou4ZEUIfGHA.5104@TK2MSFTNGP04.phx.gbl...
    >>>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>> news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl
    >>>>> OK, let me explain.
    >>>>> This is a database frontend, based on an Excel .xla add-in.
    >>>>> It connects to an Interbase database via ODBC.
    >>>>> The great majority of the reporting will be done in Excel,
    >>>>> but there is the option to output to Access.
    >>>>> So, the user runs a report from the .xla form and opts to output to an
    >>>>> Access .mdb file.
    >>>>> When the report is finished it is nice to directly launch that .mdb
    >>>>> and also open the
    >>>>> newly created table.
    >>>>> Now I can do the .mdb opening with the ShellExecute API and a simple
    >>>>> AppActivate.
    >>>>> It won't open the table though and that is the bit left to be done.
    >>>>> Hope this makes it all clear.
    >>>>
    >>>> You can probably do it like this:
    >>>>
    >>>> Dim appAccess As Object
    >>>> Dim strDatabasePath As String
    >>>> Dim strTableName As String
    >>>>
    >>>> strDatabasePath = "C:\Your Path\YourDB.mdb"
    >>>> strTableName = "YourTable"
    >>>>
    >>>> Set appAccess = CreateObject("Access.Application")
    >>>> appAccess.OpenCurrentDatabase strDatabasePath
    >>>> appAccess.Visible = True
    >>>> appAccess.DoCmd.OpenTable strTableName
    >>>> Set appAccess = Nothing
    >>>>
    >>>>
    >>>> --
    >>>> Dirk Goldgar, MS Access MVP
    >>>> www.datagnostics.com
    >>>>
    >>>> (please reply to the newsgroup)
    >>>>
    >>>>
    >>>

    >>
    >>

    >
     
  16. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    I would for sure if it was my day job.

    RBS

    "MH" <noway@nohow.co.uk> wrote in message
    news:eITcL%23OfGHA.4900@TK2MSFTNGP02.phx.gbl...
    > I'm sure you'll cope! <g>
    >
    > MH
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:eyoe2fNfGHA.2416@TK2MSFTNGP03.phx.gbl...
    >> That is exactly what I am doing already.
    >> Option to make new or use existing .mdb.
    >> Option to name the table.
    >> All working nicely.
    >>
    >> All this is just a little side issue to have a go with Access and offer
    >> the users some other option.
    >> The main thing will be to re-write a large part of my application and do
    >> all the data manipulations
    >> in Access, rather than arrays and SQL on text files. All doable, although
    >> I can see it will be a big job.
    >>
    >> RBS
    >>
    >>
    >> "MH" <noway@nohow.co.uk> wrote in message
    >> news:%23RkEnvMfGHA.2188@TK2MSFTNGP05.phx.gbl...
    >>> Glad you finally got it all sorted out RB.
    >>>
    >>> One option you may want to give your users is to select an existing MDB
    >>> file and the table name to export to, it may be better to store the
    >>> output from different queries in one mdb file. Just a suggestion of
    >>> course.
    >>>
    >>> MH
    >>>
    >>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:OnOWGlKfGHA.2076@TK2MSFTNGP04.phx.gbl...
    >>>> Thanks, that works.
    >>>> Had tried it already, but for some reason it didn't work.
    >>>> Must have done something slightly different.
    >>>> This does the trick.
    >>>>
    >>>> RBS
    >>>>
    >>>> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    >>>> news:Ou4ZEUIfGHA.5104@TK2MSFTNGP04.phx.gbl...
    >>>>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>>> news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl
    >>>>>> OK, let me explain.
    >>>>>> This is a database frontend, based on an Excel .xla add-in.
    >>>>>> It connects to an Interbase database via ODBC.
    >>>>>> The great majority of the reporting will be done in Excel,
    >>>>>> but there is the option to output to Access.
    >>>>>> So, the user runs a report from the .xla form and opts to output to
    >>>>>> an
    >>>>>> Access .mdb file.
    >>>>>> When the report is finished it is nice to directly launch that .mdb
    >>>>>> and also open the
    >>>>>> newly created table.
    >>>>>> Now I can do the .mdb opening with the ShellExecute API and a simple
    >>>>>> AppActivate.
    >>>>>> It won't open the table though and that is the bit left to be done.
    >>>>>> Hope this makes it all clear.
    >>>>>
    >>>>> You can probably do it like this:
    >>>>>
    >>>>> Dim appAccess As Object
    >>>>> Dim strDatabasePath As String
    >>>>> Dim strTableName As String
    >>>>>
    >>>>> strDatabasePath = "C:\Your Path\YourDB.mdb"
    >>>>> strTableName = "YourTable"
    >>>>>
    >>>>> Set appAccess = CreateObject("Access.Application")
    >>>>> appAccess.OpenCurrentDatabase strDatabasePath
    >>>>> appAccess.Visible = True
    >>>>> appAccess.DoCmd.OpenTable strTableName
    >>>>> Set appAccess = Nothing
    >>>>>
    >>>>>
    >>>>> --
    >>>>> Dirk Goldgar, MS Access MVP
    >>>>> www.datagnostics.com
    >>>>>
    >>>>> (please reply to the newsgroup)
    >>>>>
    >>>>>
    >>>>
    >>>
    >>>

    >>

    >
    >
     
  17. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    The Way to open the table directly is to create a macro called AutoExec &
    Add a OpenTable Command

    HTH

    Pieter

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:e55u8ZMfGHA.3888@TK2MSFTNGP04.phx.gbl...
    > Sure, I understand what you are saying, but this is just
    > for a quick display. It is not meant to be a worked-out application.
    > Updating the data won't happen. The users of this won't probably
    > know how to do it.
    > Sole purpose of the whole thing is to see data that can't be shown in
    > Excel due to having more than 65535 rows.
    >
    > Considering all of the above, what would be the advantage in showing
    > the data in a form?
    >
    > RBS
    >
    >
    > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:unyhDIMfGHA.5088@TK2MSFTNGP02.phx.gbl...
    >> Tables are meant for storing data, not for displaying the data, and
    >> certainly not for updating the data.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no private e-mails, please)
    >>
    >>
    >> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:eMgenhKfGHA.1264@TK2MSFTNGP05.phx.gbl...
    >>> Not sure what is wrong with opening a table.
    >>> All the users will want to do is see the data and maybe sort and/or
    >>> filter.
    >>> These are newly made .mdb files as the result of a SQL query run from
    >>> Excel.
    >>> So, if there has to be a form I will have to add extra code to create
    >>> this form everytime.
    >>> Still, I will have a look into this suggestion.
    >>>
    >>> RBS
    >>>
    >>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    >>> news:%23Ld3%23JIfGHA.3792@TK2MSFTNGP03.phx.gbl...
    >>>> You shouldn't be opening tables anyhow. Create a form that displays the
    >>>> data, and set that form as the Startup form (under Tools | Startup)
    >>>>
    >>>> If you can't (or won't) do that, you can create a macro named AutoExec
    >>>> that'll open the table.
    >>>>
    >>>> --
    >>>> Doug Steele, Microsoft Access MVP
    >>>> http://I.Am/DougSteele
    >>>> (no private e-mails, please)
    >>>>
    >>>>
    >>>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>> news:%230yp6WGfGHA.4900@TK2MSFTNGP02.phx.gbl...
    >>>>> OK, let me explain.
    >>>>> This is a database frontend, based on an Excel .xla add-in.
    >>>>> It connects to an Interbase database via ODBC.
    >>>>> The great majority of the reporting will be done in Excel,
    >>>>> but there is the option to output to Access.
    >>>>> So, the user runs a report from the .xla form and opts to output to an
    >>>>> Access .mdb file.
    >>>>> When the report is finished it is nice to directly launch that .mdb
    >>>>> and also open the
    >>>>> newly created table.
    >>>>> Now I can do the .mdb opening with the ShellExecute API and a simple
    >>>>> AppActivate.
    >>>>> It won't open the table though and that is the bit left to be done.
    >>>>> Hope this makes it all clear.
    >>>>>
    >>>>> RBS
    >>>>>
    >>>>>
    >>>>> "MH" <noway@nohow.co.uk> wrote in message
    >>>>> news:OECFvJGfGHA.2188@TK2MSFTNGP05.phx.gbl...
    >>>>>> You may be going about this whole thing the wrong way, usually a
    >>>>>> database (your .mdb file) is set up in advance with all the tables,
    >>>>>> queries, forms, reports and code it needs to do the job already
    >>>>>> created. Could you give some kind of overall view of what you're
    >>>>>> trying to achieve? Maybe someone can come up with the "ideal"
    >>>>>> solution for you (obviously we'd leave all the difficult stuff for
    >>>>>> you to figure out though!)
    >>>>>>
    >>>>>> Regards
    >>>>>>
    >>>>>> MH
    >>>>>>
    >>>>>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>>>> news:%23vpblZFfGHA.2172@TK2MSFTNGP04.phx.gbl...
    >>>>>>> Just want to do the as I would do manually:
    >>>>>>> double-click the .mdb
    >>>>>>> double-click the table, so it open in data view.
    >>>>>>>
    >>>>>>> Tried your code, but get:
    >>>>>>> Object variable or With block variable not set (Error 91)
    >>>>>>> at the line:
    >>>>>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>>>>
    >>>>>>> Can't I just automate Access and open the table with the Access
    >>>>>>> methods?
    >>>>>>> Not sure why I have to run SQL if the table has been made already.
    >>>>>>> I am new to Access, so maybe I overlook some fundamental things.
    >>>>>>>
    >>>>>>> RBS
    >>>>>>>
    >>>>>>> "Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in
    >>>>>>> message news:39439491-A370-4683-9EBD-BA783D2615E9@microsoft.com...
    >>>>>>>> It depends in what way you want to 'open' the table. Having
    >>>>>>>> returned a
    >>>>>>>> reference to the external database with the OpenDatabase methods
    >>>>>>>> you can
    >>>>>>>> return a reference to any of its tables via its TableDefs
    >>>>>>>> collection. You
    >>>>>>>> can then establish a recordsdet using the OpenRecordset method and
    >>>>>>>> then
    >>>>>>>> iterate through the recordset in code, search for a row in the
    >>>>>>>> recordset etc.
    >>>>>>>>
    >>>>>>>> If, however, you simply want to open the table in datasheet view
    >>>>>>>> then this
    >>>>>>>> won't do that. You could create a link to the external table in
    >>>>>>>> code using
    >>>>>>>> the TransferDatabase method and then open the linked table.
    >>>>>>>> Another method
    >>>>>>>> would be to create a temporary query with an IN clause which
    >>>>>>>> references the
    >>>>>>>> external database and open that, e.g.
    >>>>>>>>
    >>>>>>>> Dim dbs As DAO.Database, qdf As DAO.QueryDef
    >>>>>>>> Dim strSQL As String
    >>>>>>>> Dim strPath As String
    >>>>>>>>
    >>>>>>>> strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
    >>>>>>>> strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"
    >>>>>>>>
    >>>>>>>> Set dbs = CurrentDb
    >>>>>>>> Set qdf = dbs.CreateQueryDef("Temp", strSQL)
    >>>>>>>>
    >>>>>>>> DoCmd.OpenQuery "Temp"
    >>>>>>>>
    >>>>>>>> dbs.QueryDefs.Delete "Temp"
    >>>>>>>>
    >>>>>>>> Note that the path to the external file needs to be in quotes in
    >>>>>>>> the SQL
    >>>>>>>> statement so when concatenating the strPath variable into the
    >>>>>>>> string
    >>>>>>>> expression doubled quotes are used as these when used within a
    >>>>>>>> string
    >>>>>>>> delimited by quotes evaluate to a quotes character.
    >>>>>>>>
    >>>>>>>> Ken Sheridan
    >>>>>>>> Stafford, England
    >>>>>>>>
    >>>>>>>> "RB Smissaert" wrote:
    >>>>>>>>
    >>>>>>>>> Given I created/updated a .mdb file like with:
    >>>>>>>>>
    >>>>>>>>> Dim db As DAO.Database
    >>>>>>>>>
    >>>>>>>>> Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
    >>>>>>>>> or
    >>>>>>>>> Set db = DBEngine.OpenDatabase(strMDBPath)
    >>>>>>>>>
    >>>>>>>>> How do I launch this .mdb file and open a specified table?
    >>>>>>>>> I could do it with Shell I suppose (not sure about the table
    >>>>>>>>> though), but
    >>>>>>>>> given
    >>>>>>>>> the above code there might be a better way.
    >>>>>>>>>
    >>>>>>>>> RBS
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>>
    >>>

    >>
    >>

    >
     

Share This Page