Welcome to SPN

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

Sign Up Now!

Interbase data to Access

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

  1. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    What would be the best (fastest) way to:
    create a new .mdb file with a table with specified fields.
    run a SQL query on an Interbase database via ODBC.
    Put the rows produced by this query in the Access table.

    I think this would work with looping through a recordset, but
    would it somehow be possible to do it directly with an INSERT
    statement?

    I have to do this from Excel VBA.
    Thanks for any advice.

    RBS
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:uCmhk83eGHA.1856@TK2MSFTNGP03.phx.gbl
    > What would be the best (fastest) way to:
    > create a new .mdb file with a table with specified fields.
    > run a SQL query on an Interbase database via ODBC.
    > Put the rows produced by this query in the Access table.
    >
    > I think this would work with looping through a recordset, but
    > would it somehow be possible to do it directly with an INSERT
    > statement?
    >
    > I have to do this from Excel VBA.


    I think you should be able to do this using DAO from the Excel code
    module itself, without even automating a copy of Access. You can
    provide the connect string for the Interbase database in the SQL string
    that you execute. If you can create the table you want using a CREATE
    TABLE statement, the only DAO methods you'd need are
    DBEngine.CreateDatabase and Database.Execute (to execute the SQL
    statements).

    You might use a make-table query instead of a CREATE TABLE statement and
    an append query, but then you wouldn't have as much control over the
    field types in the resulting Jet table.

    To extract data from the Interbase database, you'd use the IN clause to
    provide the connect string.

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

    (please reply to the newsgroup)
     
  4. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Thanks, that sounds promising.

    Currently I start with a ADO recordset from the Interbase data like this:

    Set rs = New ADODB.Recordset

    rs.Open Source:=strQuery, _
    ActiveConnection:=ADOConn, _
    CursorType:=adOpenForwardOnly, _
    LockType:=adLockReadOnly, _
    Options:=adCmdText

    I can see how in VBA I can create the Access table with the specified
    fields, but not sure
    what the SQL should look like to retrieve the IB data and put the data in
    the Access table.
    Any code snippets/examples?

    RBS



    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:%23DE6DJ4eGHA.5040@TK2MSFTNGP03.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:uCmhk83eGHA.1856@TK2MSFTNGP03.phx.gbl
    >> What would be the best (fastest) way to:
    >> create a new .mdb file with a table with specified fields.
    >> run a SQL query on an Interbase database via ODBC.
    >> Put the rows produced by this query in the Access table.
    >>
    >> I think this would work with looping through a recordset, but
    >> would it somehow be possible to do it directly with an INSERT
    >> statement?
    >>
    >> I have to do this from Excel VBA.

    >
    > I think you should be able to do this using DAO from the Excel code
    > module itself, without even automating a copy of Access. You can
    > provide the connect string for the Interbase database in the SQL string
    > that you execute. If you can create the table you want using a CREATE
    > TABLE statement, the only DAO methods you'd need are
    > DBEngine.CreateDatabase and Database.Execute (to execute the SQL
    > statements).
    >
    > You might use a make-table query instead of a CREATE TABLE statement and
    > an append query, but then you wouldn't have as much control over the
    > field types in the resulting Jet table.
    >
    > To extract data from the Interbase database, you'd use the IN clause to
    > provide the connect string.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  5. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    The solutions seems to be a pass-through query, but I just
    can't find any example of how these queries should be constructed.

    RBS

    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:%23DE6DJ4eGHA.5040@TK2MSFTNGP03.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:uCmhk83eGHA.1856@TK2MSFTNGP03.phx.gbl
    >> What would be the best (fastest) way to:
    >> create a new .mdb file with a table with specified fields.
    >> run a SQL query on an Interbase database via ODBC.
    >> Put the rows produced by this query in the Access table.
    >>
    >> I think this would work with looping through a recordset, but
    >> would it somehow be possible to do it directly with an INSERT
    >> statement?
    >>
    >> I have to do this from Excel VBA.

    >
    > I think you should be able to do this using DAO from the Excel code
    > module itself, without even automating a copy of Access. You can
    > provide the connect string for the Interbase database in the SQL string
    > that you execute. If you can create the table you want using a CREATE
    > TABLE statement, the only DAO methods you'd need are
    > DBEngine.CreateDatabase and Database.Execute (to execute the SQL
    > statements).
    >
    > You might use a make-table query instead of a CREATE TABLE statement and
    > an append query, but then you wouldn't have as much control over the
    > field types in the resulting Jet table.
    >
    > To extract data from the Interbase database, you'd use the IN clause to
    > provide the connect string.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  6. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23xTUXQ4eGHA.2032@TK2MSFTNGP02.phx.gbl
    > Thanks, that sounds promising.
    >
    > Currently I start with a ADO recordset from the Interbase data like
    > this:
    >
    > Set rs = New ADODB.Recordset
    >
    > rs.Open Source:=strQuery, _
    > ActiveConnection:=ADOConn, _
    > CursorType:=adOpenForwardOnly, _
    > LockType:=adLockReadOnly, _
    > Options:=adCmdText
    >
    > I can see how in VBA I can create the Access table with the specified
    > fields, but not sure
    > what the SQL should look like to retrieve the IB data and put the
    > data in the Access table.
    > Any code snippets/examples?


    What I was proposing wouldn't involve recordsets at all, whether of the
    ADODB or DAO flavor. I was talking about something roughly like this:

    '----- start of example code -----
    Dim db As DAO.Database
    Dim strConnect As String

    strConnect = "<connect string to Interbase DB>"

    Set db = DBEngine.CreateDatabase( _
    "C:\Your Path\YourDB.mdb", _
    dbLangGeneral)

    With db

    .Execute _
    "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    dbFailOnError

    .Execute _
    "INSERT INTO tblFoo (Field1, Field2) " & _
    "SELECT F1, F2 FROM SomeTable IN """" [" & _
    strConnect & _
    "]", _
    dbFailOnError

    .Close

    End With

    Set db = Nothing
    '----- end of example code -----

    I don't know what the connect string for your Interbase database would
    have to look like, and I'm not sure I've got the exact syntax for that
    right, but executing a query like this ought to be a lot simpler and
    more efficient that opening two recordsets, one for each table, and
    copying record by record from one to the other.

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

    (please reply to the newsgroup)
     
  7. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Yes, I understand I shouldn't use RecordSets in this situation, but this is
    what I use now. Not sure now why I mentioned it. I think it was to show
    that I have a working connection to Interbase.

    Will have a go with your code now and see how far I get.

    RBS



    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:OGpX7k4eGHA.3996@TK2MSFTNGP04.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23xTUXQ4eGHA.2032@TK2MSFTNGP02.phx.gbl
    >> Thanks, that sounds promising.
    >>
    >> Currently I start with a ADO recordset from the Interbase data like
    >> this:
    >>
    >> Set rs = New ADODB.Recordset
    >>
    >> rs.Open Source:=strQuery, _
    >> ActiveConnection:=ADOConn, _
    >> CursorType:=adOpenForwardOnly, _
    >> LockType:=adLockReadOnly, _
    >> Options:=adCmdText
    >>
    >> I can see how in VBA I can create the Access table with the specified
    >> fields, but not sure
    >> what the SQL should look like to retrieve the IB data and put the
    >> data in the Access table.
    >> Any code snippets/examples?

    >
    > What I was proposing wouldn't involve recordsets at all, whether of the
    > ADODB or DAO flavor. I was talking about something roughly like this:
    >
    > '----- start of example code -----
    > Dim db As DAO.Database
    > Dim strConnect As String
    >
    > strConnect = "<connect string to Interbase DB>"
    >
    > Set db = DBEngine.CreateDatabase( _
    > "C:\Your Path\YourDB.mdb", _
    > dbLangGeneral)
    >
    > With db
    >
    > .Execute _
    > "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    > dbFailOnError
    >
    > .Execute _
    > "INSERT INTO tblFoo (Field1, Field2) " & _
    > "SELECT F1, F2 FROM SomeTable IN """" [" & _
    > strConnect & _
    > "]", _
    > dbFailOnError
    >
    > .Close
    >
    > End With
    >
    > Set db = Nothing
    > '----- end of example code -----
    >
    > I don't know what the connect string for your Interbase database would
    > have to look like, and I'm not sure I've got the exact syntax for that
    > right, but executing a query like this ought to be a lot simpler and
    > more efficient that opening two recordsets, one for each table, and
    > copying record by record from one to the other.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  8. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    What should this bit be:
    IN """"
    I take the """" has to be replaced by something else.

    Another thing, I have a working ADO connection to the
    IB database. Couldn't I use that? Or can't I because your
    code uses DAO?

    RBS

    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:OGpX7k4eGHA.3996@TK2MSFTNGP04.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23xTUXQ4eGHA.2032@TK2MSFTNGP02.phx.gbl
    >> Thanks, that sounds promising.
    >>
    >> Currently I start with a ADO recordset from the Interbase data like
    >> this:
    >>
    >> Set rs = New ADODB.Recordset
    >>
    >> rs.Open Source:=strQuery, _
    >> ActiveConnection:=ADOConn, _
    >> CursorType:=adOpenForwardOnly, _
    >> LockType:=adLockReadOnly, _
    >> Options:=adCmdText
    >>
    >> I can see how in VBA I can create the Access table with the specified
    >> fields, but not sure
    >> what the SQL should look like to retrieve the IB data and put the
    >> data in the Access table.
    >> Any code snippets/examples?

    >
    > What I was proposing wouldn't involve recordsets at all, whether of the
    > ADODB or DAO flavor. I was talking about something roughly like this:
    >
    > '----- start of example code -----
    > Dim db As DAO.Database
    > Dim strConnect As String
    >
    > strConnect = "<connect string to Interbase DB>"
    >
    > Set db = DBEngine.CreateDatabase( _
    > "C:\Your Path\YourDB.mdb", _
    > dbLangGeneral)
    >
    > With db
    >
    > .Execute _
    > "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    > dbFailOnError
    >
    > .Execute _
    > "INSERT INTO tblFoo (Field1, Field2) " & _
    > "SELECT F1, F2 FROM SomeTable IN """" [" & _
    > strConnect & _
    > "]", _
    > dbFailOnError
    >
    > .Close
    >
    > End With
    >
    > Set db = Nothing
    > '----- end of example code -----
    >
    > I don't know what the connect string for your Interbase database would
    > have to look like, and I'm not sure I've got the exact syntax for that
    > right, but executing a query like this ought to be a lot simpler and
    > more efficient that opening two recordsets, one for each table, and
    > copying record by record from one to the other.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  9. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:u1Q8bq4eGHA.956@TK2MSFTNGP05.phx.gbl
    > Yes, I understand I shouldn't use RecordSets in this situation, but
    > this is what I use now. Not sure now why I mentioned it. I think it
    > was to show that I have a working connection to Interbase.
    >
    > Will have a go with your code now and see how far I get.


    Let me know how it goes.

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

    (please reply to the newsgroup)
     
  10. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:eqdiTw4eGHA.3488@TK2MSFTNGP02.phx.gbl
    > What should this bit be:
    > IN """"
    > I take the """" has to be replaced by something else.


    No, actually it's part of the syntax of the statement, and should be
    left as is. See the help entry for the IN clause in the Jet SQL
    Reference section of the help file.

    > Another thing, I have a working ADO connection to the
    > IB database. Couldn't I use that? Or can't I because your
    > code uses DAO?


    I don't see how you could use that, except that you may be able to get
    the connection string from that connection to use in your SQL statement.
    However, I'm not sure that DAO can use the OleDB connection that ADO
    uses, so you may need to build an ODBC connect string.

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

    (please reply to the newsgroup)
     
  11. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Yes, I am stuck on this bit:
    IN """"
    Not sure what is going on there.

    This is my connection string:
    strConnect = "DSN=System 6000;UID=sysdba;PWD=********"

    But this applies to my current ADO connection, not sure it can work with
    DAO.

    RBS


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:e71g9x4eGHA.3364@TK2MSFTNGP05.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:u1Q8bq4eGHA.956@TK2MSFTNGP05.phx.gbl
    >> Yes, I understand I shouldn't use RecordSets in this situation, but
    >> this is what I use now. Not sure now why I mentioned it. I think it
    >> was to show that I have a working connection to Interbase.
    >>
    >> Will have a go with your code now and see how far I get.

    >
    > Let me know how it goes.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  12. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    OK, thanks, then it must be my connection string at fault.

    This is my code:

    Sub test()

    Dim db As DAO.Database
    Dim strConnect As String

    On Error Resume Next
    Kill "C:\test.mdb"
    On Error GoTo 0

    strConnect = "DSN=System 6000;UID=sysdba;PWD=torexkey"

    Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)

    With db

    .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    dbFailOnError

    .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    strConnect & _
    "]", _
    dbFailOnError

    .Close

    End With

    Set db = Nothing

    End Sub


    And this is the error message:
    Runtime error 3321, No database specified in connection string or IN clause.
    So, yes that must be my faulty connection string.
    Will get the full ODBC string that includes the db path.

    RBS


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:OK6S%2304eGHA.1204@TK2MSFTNGP02.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:eqdiTw4eGHA.3488@TK2MSFTNGP02.phx.gbl
    >> What should this bit be:
    >> IN """"
    >> I take the """" has to be replaced by something else.

    >
    > No, actually it's part of the syntax of the statement, and should be
    > left as is. See the help entry for the IN clause in the Jet SQL
    > Reference section of the help file.
    >
    >> Another thing, I have a working ADO connection to the
    >> IB database. Couldn't I use that? Or can't I because your
    >> code uses DAO?

    >
    > I don't see how you could use that, except that you may be able to get
    > the connection string from that connection to use in your SQL statement.
    > However, I'm not sure that DAO can use the OleDB connection that ADO
    > uses, so you may need to build an ODBC connect string.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  13. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:e%23SdV74eGHA.1880@TK2MSFTNGP02.phx.gbl
    > OK, thanks, then it must be my connection string at fault.
    >
    > This is my code:
    >
    > Sub test()
    >
    > Dim db As DAO.Database
    > Dim strConnect As String
    >
    > On Error Resume Next
    > Kill "C:\test.mdb"
    > On Error GoTo 0
    >
    > strConnect = [deleted by Dirk]"
    >
    > Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)
    >
    > With db
    >
    > .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    > dbFailOnError
    >
    > .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    > "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    > strConnect & _
    > "]", _
    > dbFailOnError
    >
    > .Close
    >
    > End With
    >
    > Set db = Nothing
    >
    > End Sub
    >
    >
    > And this is the error message:
    > Runtime error 3321, No database specified in connection string or IN
    > clause. So, yes that must be my faulty connection string.
    > Will get the full ODBC string that includes the db path.


    That's probably not necessary. But you need to specify that it's an
    ODBC connection:

    strConnect = "ODBC;DSN=System 6000;UID=xxxxxxx;PWD=yyyyyyy"


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

    (please reply to the newsgroup)
     
  14. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Now have this connection string:

    strConnect = "Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};" & _
    "Server=" & "local" & ";" & _
    "Database=" & "C:\Torex\Synergy\Meddata\S6000\db\s6.gdb" &
    ";" & _
    "Uid=" & "sysdba" & ";" & _
    "Pwd=" & "*****" & ";"

    But get error:
    Could not find installable ISAM. (Error 3170)
    This string does work with ADO, but I suppose it will have to be different.
    Will just search now for the DAO connection string with this IB ODBC driver.

    RBS


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:OK6S%2304eGHA.1204@TK2MSFTNGP02.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:eqdiTw4eGHA.3488@TK2MSFTNGP02.phx.gbl
    >> What should this bit be:
    >> IN """"
    >> I take the """" has to be replaced by something else.

    >
    > No, actually it's part of the syntax of the statement, and should be
    > left as is. See the help entry for the IN clause in the Jet SQL
    > Reference section of the help file.
    >
    >> Another thing, I have a working ADO connection to the
    >> IB database. Couldn't I use that? Or can't I because your
    >> code uses DAO?

    >
    > I don't see how you could use that, except that you may be able to get
    > the connection string from that connection to use in your SQL statement.
    > However, I'm not sure that DAO can use the OleDB connection that ADO
    > uses, so you may need to build an ODBC connect string.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  15. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Amazing, that does work indeed!
    Got my data in the .mdb now.
    Only thing is that a IB login box pops up, which is of course no good.
    Would there be any parameters in the connection string to avoid that?

    RBS

    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:Oc07OA5eGHA.380@TK2MSFTNGP04.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:e%23SdV74eGHA.1880@TK2MSFTNGP02.phx.gbl
    >> OK, thanks, then it must be my connection string at fault.
    >>
    >> This is my code:
    >>
    >> Sub test()
    >>
    >> Dim db As DAO.Database
    >> Dim strConnect As String
    >>
    >> On Error Resume Next
    >> Kill "C:\test.mdb"
    >> On Error GoTo 0
    >>
    >> strConnect = [deleted by Dirk]"
    >>
    >> Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)
    >>
    >> With db
    >>
    >> .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    >> dbFailOnError
    >>
    >> .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    >> "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    >> strConnect & _
    >> "]", _
    >> dbFailOnError
    >>
    >> .Close
    >>
    >> End With
    >>
    >> Set db = Nothing
    >>
    >> End Sub
    >>
    >>
    >> And this is the error message:
    >> Runtime error 3321, No database specified in connection string or IN
    >> clause. So, yes that must be my faulty connection string.
    >> Will get the full ODBC string that includes the db path.

    >
    > That's probably not necessary. But you need to specify that it's an
    > ODBC connection:
    >
    > strConnect = "ODBC;DSN=System 6000;UID=xxxxxxx;PWD=yyyyyyy"
    >
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  16. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    OK, got this now without the login popping up, so seem all worked out now:


    Sub test()

    Dim db As DAO.Database
    Dim strConnect As String
    Dim strDBPath As String
    Dim strExtra As String

    strDBPath = "C:\Torex\Synergy\Meddata\S6000\db\s6.gdb"

    strExtra = "DB=" & strDBPath & ";" & _
    "OLDMETADATA=1;"

    On Error Resume Next
    Kill "C:\test.mdb"
    On Error GoTo 0

    strConnect = "ODBC;DSN=System 6000;UID=sysdba;PWD=*******;" & strExtra

    Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)

    With db

    .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    dbFailOnError

    .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    strConnect & _
    "]", _
    dbFailOnError

    .Close

    End With

    Set db = Nothing

    End Sub

    Second time I run it it is much faster, but I take that that is because the
    connection stayed alive
    and this particular query is very fast, so the time taken first time was in
    making the connection.

    I suppose my joins and WHERE clause will come between STAFF and IN in the
    above query.
    Will try that now.
    This looks all nice and simple and thanks again.


    RBS


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:Oc07OA5eGHA.380@TK2MSFTNGP04.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:e%23SdV74eGHA.1880@TK2MSFTNGP02.phx.gbl
    >> OK, thanks, then it must be my connection string at fault.
    >>
    >> This is my code:
    >>
    >> Sub test()
    >>
    >> Dim db As DAO.Database
    >> Dim strConnect As String
    >>
    >> On Error Resume Next
    >> Kill "C:\test.mdb"
    >> On Error GoTo 0
    >>
    >> strConnect = [deleted by Dirk]"
    >>
    >> Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)
    >>
    >> With db
    >>
    >> .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    >> dbFailOnError
    >>
    >> .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    >> "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    >> strConnect & _
    >> "]", _
    >> dbFailOnError
    >>
    >> .Close
    >>
    >> End With
    >>
    >> Set db = Nothing
    >>
    >> End Sub
    >>
    >>
    >> And this is the error message:
    >> Runtime error 3321, No database specified in connection string or IN
    >> clause. So, yes that must be my faulty connection string.
    >> Will get the full ODBC string that includes the db path.

    >
    > That's probably not necessary. But you need to specify that it's an
    > ODBC connection:
    >
    > strConnect = "ODBC;DSN=System 6000;UID=xxxxxxx;PWD=yyyyyyy"
    >
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  17. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Still can't see how I can use a join and a where clause in
    this construction. The problem seems to be that the IN
    now is considered as the SQL key word IN as in:
    where E.READ_CODE IN ('G2...', 'G3...', G6...') etc.

    RBS


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:Oc07OA5eGHA.380@TK2MSFTNGP04.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:e%23SdV74eGHA.1880@TK2MSFTNGP02.phx.gbl
    >> OK, thanks, then it must be my connection string at fault.
    >>
    >> This is my code:
    >>
    >> Sub test()
    >>
    >> Dim db As DAO.Database
    >> Dim strConnect As String
    >>
    >> On Error Resume Next
    >> Kill "C:\test.mdb"
    >> On Error GoTo 0
    >>
    >> strConnect = [deleted by Dirk]"
    >>
    >> Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)
    >>
    >> With db
    >>
    >> .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    >> dbFailOnError
    >>
    >> .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    >> "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    >> strConnect & _
    >> "]", _
    >> dbFailOnError
    >>
    >> .Close
    >>
    >> End With
    >>
    >> Set db = Nothing
    >>
    >> End Sub
    >>
    >>
    >> And this is the error message:
    >> Runtime error 3321, No database specified in connection string or IN
    >> clause. So, yes that must be my faulty connection string.
    >> Will get the full ODBC string that includes the db path.

    >
    > That's probably not necessary. But you need to specify that it's an
    > ODBC connection:
    >
    > strConnect = "ODBC;DSN=System 6000;UID=xxxxxxx;PWD=yyyyyyy"
    >
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  18. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    OK, figured out now how to handle the WHERE clause:


    Sub test()

    Dim db As DAO.Database
    Dim strConnect As String
    Dim strDBPath As String
    Dim strExtra As String
    Dim strQuery As String
    Dim strQuery2 As String
    Dim strQuery3 As String

    strDBPath = "C:\Torex\Synergy\Meddata\S6000\db\s6.gdb"

    strExtra = "DB=" & strDBPath & ";" & _
    "OLDMETADATA=1;"

    strQuery = "SELECT E.READ_CODE, E.TERM_TEXT " & _
    "FROM ENTRY E INNER JOIN PATIENT P ON " & _
    "(E.PATIENT_ID = P.PATIENT_ID) " & _
    "WHERE E.READ_CODE = 'G58..' AND " & _
    "P.MAIN_REG_TYPE = 1"

    strQuery2 = "SELECT E.READ_CODE, E.TERM_TEXT " & _
    "FROM ENTRY E INNER JOIN PATIENT P ON " & _
    "(E.PATIENT_ID = P.PATIENT_ID) "

    strQuery3 = " WHERE E.READ_CODE = 'G58..' AND " & _
    "NOT P.MAIN_REG_TYPE = 1"

    On Error Resume Next
    Kill "C:\test.mdb"
    On Error GoTo 0

    strConnect = "ODBC;DSN=System 6000;UID=sysdba;PWD=torexkey;" & strExtra

    Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)

    With db

    .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    dbFailOnError

    '.Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    strConnect & _
    "]", _
    dbFailOnError

    '.Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    strQuery & " IN """" [" & _
    strConnect & _
    "]", _
    dbFailOnError

    .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    strQuery2 & " IN """" [" & _
    strConnect & _
    "]" & strQuery3, _
    dbFailOnError

    .Close

    End With

    Set db = Nothing

    End Sub


    So, I think that will be it then.
    This is only the very start though of the possible re-write of my app, which
    I mentioned in the post
    Project advice in this NG.


    RBS

    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:Oc07OA5eGHA.380@TK2MSFTNGP04.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:e%23SdV74eGHA.1880@TK2MSFTNGP02.phx.gbl
    >> OK, thanks, then it must be my connection string at fault.
    >>
    >> This is my code:
    >>
    >> Sub test()
    >>
    >> Dim db As DAO.Database
    >> Dim strConnect As String
    >>
    >> On Error Resume Next
    >> Kill "C:\test.mdb"
    >> On Error GoTo 0
    >>
    >> strConnect = [deleted by Dirk]"
    >>
    >> Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)
    >>
    >> With db
    >>
    >> .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    >> dbFailOnError
    >>
    >> .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    >> "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    >> strConnect & _
    >> "]", _
    >> dbFailOnError
    >>
    >> .Close
    >>
    >> End With
    >>
    >> Set db = Nothing
    >>
    >> End Sub
    >>
    >>
    >> And this is the error message:
    >> Runtime error 3321, No database specified in connection string or IN
    >> clause. So, yes that must be my faulty connection string.
    >> Will get the full ODBC string that includes the db path.

    >
    > That's probably not necessary. But you need to specify that it's an
    > ODBC connection:
    >
    > strConnect = "ODBC;DSN=System 6000;UID=xxxxxxx;PWD=yyyyyyy"
    >
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  19. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    All nicely worked out now:


    Function GetSelectFields(strSelectFrom As String) As Variant

    Dim arr
    Dim strSelect As String
    Dim lStart As Long
    Dim lEnd As Long
    Dim i As Long

    lStart = InStr(6, strSelectFrom, " ", vbBinaryCompare) + 1
    lEnd = InStr(lStart, UCase(strSelectFrom), " FROM", vbBinaryCompare)
    strSelect = Replace(Trim(Mid$(strSelectFrom, _
    lStart, _
    lEnd - lStart)), _
    " ", _
    "", _
    1, _
    -1, _
    vbBinaryCompare)

    '£ is one of the very few non-letter chars allowed, other one is
    under-score
    '---------------------------------------------------------------------------------------
    If InStr(1, strSelect, ",", vbBinaryCompare) = 0 Then
    GetSelectFields = Replace(strSelect, ".", "£", 1, -1, vbBinaryCompare)
    Else
    arr = Split(strSelect, ",")
    For i = 0 To UBound(arr)
    arr(i) = Replace(arr(i), ".", "£", 1, -1, vbBinaryCompare)
    Next
    GetSelectFields = arr
    End If

    End Function

    Function MakeFieldDefs(arrFields As Variant, _
    arrTypes As Variant, _
    lFieldCount As Long, _
    bAddTypes As Boolean) As String

    Dim i As Long
    Dim strTemp As String

    If bAddTypes Then
    If lFieldCount = 1 Then
    MakeFieldDefs = "(" & arrFields & " " & arrTypes & ")"
    Else
    strTemp = "(" & arrFields(0) & " " & arrTypes(0) & ", "
    For i = 1 To UBound(arrFields)
    If i = UBound(arrFields) Then
    strTemp = strTemp & arrFields(i) & " " & arrTypes(i) & ")"
    Else
    strTemp = strTemp & arrFields(i) & " " & arrTypes(i) & ", "
    End If
    Next
    End If
    Else
    If lFieldCount = 1 Then
    MakeFieldDefs = " (" & arrFields & ") "
    Else
    strTemp = "(" & arrFields(0) & ", "
    For i = 1 To UBound(arrFields)
    If i = UBound(arrFields) Then
    strTemp = strTemp & arrFields(i) & ") "
    Else
    strTemp = strTemp & arrFields(i) & ", "
    End If
    Next
    End If
    End If

    MakeFieldDefs = strTemp

    End Function

    Sub Test()

    Dim strMDBPath As String
    Dim strUN As String
    Dim strPW As String
    Dim strDSN As String
    Dim strDBPath As String
    Dim lOLDMETADATA As Long
    Dim strSelectFrom As String
    Dim strWhere As String
    Dim strTable As String
    Dim arrTypes As Variant

    strMDBPath = "C:\test.mdb"
    strUN = "sysdba"
    strPW = "********"
    strDSN = "System 6000"
    strDBPath = "C:\Torex\Synergy\Meddata\S6000\db\s6.gdb"
    lOLDMETADATA = 1
    strTable = "TestTable"
    strSelectFrom = "SELECT E.READ_CODE, E.TERM_TEXT " & _
    "FROM ENTRY E INNER JOIN PATIENT P ON " & _
    "(E.PATIENT_ID = P.PATIENT_ID) "
    strWhere = " WHERE E.READ_CODE = 'G3...' AND " & _
    "NOT P.MAIN_REG_TYPE = 1"
    arrTypes = Array("CHAR", "CHAR")

    IB2Access strMDBPath, _
    strUN, _
    strPW, _
    strDSN, _
    strDBPath, _
    lOLDMETADATA, strSelectFrom, strWhere, _
    strTable, _
    arrTypes, _
    2

    End Sub

    Sub IB2Access(strMDBPath As String, _
    strUN As String, _
    strPW As String, _
    strDSN As String, _
    strDBPath As String, _
    lOLDMETADATA As Long, _
    strSelectFrom As String, _
    strWhere As String, _
    strTable As String, _
    arrTypes As Variant, _
    lFieldCount As Long)

    Dim db As DAO.Database
    Dim strConnect As String
    Dim arrFields
    Dim strFieldDefs As String

    arrFields = GetSelectFields(strSelectFrom)

    On Error Resume Next
    Kill strMDBPath
    On Error GoTo 0

    strConnect = "ODBC;" & _
    "DSN=" & strDSN & ";" & _
    "UID=" & strUN & ";" & _
    "PWD=" & strPW & ";" & _
    "DB=" & strDBPath & ";" & _
    "OLDMETADATA=" & lOLDMETADATA & ";"

    Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)

    With db

    strFieldDefs = MakeFieldDefs(arrFields, arrTypes, lFieldCount, True)
    .Execute "CREATE TABLE " & _
    strTable & _
    strFieldDefs, _
    dbFailOnError

    strFieldDefs = MakeFieldDefs(arrFields, arrTypes, lFieldCount, False)
    .Execute "INSERT INTO " & _
    strTable & _
    strFieldDefs & _
    strSelectFrom & _
    " IN """" [" & strConnect & "]" & _
    strWhere, _
    dbFailOnError

    .Close

    End With

    Set db = Nothing

    End Sub


    RBS


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:Oc07OA5eGHA.380@TK2MSFTNGP04.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:e%23SdV74eGHA.1880@TK2MSFTNGP02.phx.gbl
    >> OK, thanks, then it must be my connection string at fault.
    >>
    >> This is my code:
    >>
    >> Sub test()
    >>
    >> Dim db As DAO.Database
    >> Dim strConnect As String
    >>
    >> On Error Resume Next
    >> Kill "C:\test.mdb"
    >> On Error GoTo 0
    >>
    >> strConnect = [deleted by Dirk]"
    >>
    >> Set db = DBEngine.CreateDatabase("C:\test.mdb", dbLangGeneral)
    >>
    >> With db
    >>
    >> .Execute "CREATE TABLE tblFoo (Field1 CHAR, Field2 CHAR)", _
    >> dbFailOnError
    >>
    >> .Execute "INSERT INTO tblFoo (Field1, Field2) " & _
    >> "SELECT STAFF_ID, SURNAME FROM STAFF IN """" [" & _
    >> strConnect & _
    >> "]", _
    >> dbFailOnError
    >>
    >> .Close
    >>
    >> End With
    >>
    >> Set db = Nothing
    >>
    >> End Sub
    >>
    >>
    >> And this is the error message:
    >> Runtime error 3321, No database specified in connection string or IN
    >> clause. So, yes that must be my faulty connection string.
    >> Will get the full ODBC string that includes the db path.

    >
    > That's probably not necessary. But you need to specify that it's an
    > ODBC connection:
    >
    > strConnect = "ODBC;DSN=System 6000;UID=xxxxxxx;PWD=yyyyyyy"
    >
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
  20. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23PWsBd6eGHA.4932@TK2MSFTNGP03.phx.gbl
    > All nicely worked out now


    I see you've been very busy. Nice work! It's a pleasure to work with
    you.

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

    (please reply to the newsgroup)
     
  21. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Would you be interested to comment on the posting from
    me dated 19 May: Project advice?
    This question was really the start of that.

    RBS


    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:OO7F4e8eGHA.356@TK2MSFTNGP02.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23PWsBd6eGHA.4932@TK2MSFTNGP03.phx.gbl
    >> All nicely worked out now

    >
    > I see you've been very busy. Nice work! It's a pleasure to work with
    > you.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     

Share This Page