Results 1 to 15 of 15
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    ODBC, Importing Tables (A2K, SR1)

    Any ideas on how to properly format the code to import several tables to an access database Via ODBC. The tables reside on a network located on a different drive, however, same server. I can do this directly in access via Get External Data and selecting ODBC (a machine data source is already set up and functioning to do this) however, I would prefer to automate this process.

    I have been looking at the ODBC Connect Str command but am unsure if this will do what I need.

    Thanks for your help. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC, Importing Tables (A2K, SR1)

    Are you trying to import or to link the tables, and did you want to use DAO or ADO to handle it?
    Charlotte

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    Charlotte,

    I am currently importing the tables. As to using DAO or ADO to handle it, I will need to read up on this and provide you with an answer on Monday. (I can never get them straight anyway)
    .... More food for thought
    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    Charlotte,

    ... As to ADO or DAO, I am not sure which I should use. As I am also an Oracle SQL programmer (among other things), would one of them be similar in format and thus, easier to use? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC, Importing Tables (A2K, SR1)

    I'm clueless on Oracle, but neither of them is really like SQL Server, so maybe that answers your question. DAO is more structured and linear and is built to handle Jet database structures. ADO is more flexible, handles almost any kind of data, and it's methods and properties depend largely on the particular provider. DAO is easier to learn because there's normally only one "right" way to do things. With ADO, there are multiple right ways, take your pick.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Mar 2003
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    Here is some code that I use with linked Oracle tables, maybe it will help. I first created a table with the following fields:
    Database
    UID
    PWD
    Description
    ODBCTableName
    LocalTableName
    DSN
    The LocalTableName field is my Key

    Function DoesOracleTblExist(strTblName As String) As Boolean
    'On Error Resume Next
    Dim db As Database, tbl As TableDef
    Set db = CurrentDb
    Set tbl = db.TableDefs(strTblName)
    If Err.Number = 3265 Then ' Item not found.
    DoesOracleTblExist = False
    Exit Function
    End If
    DoesOracleTblExist = True
    End Function

    Function CreateODBCLinkedOracleTables() As Boolean
    On Error GoTo CreateODBCLinkedOracleTables_Err
    Dim strTblName As String, strConn As String
    Dim db As Database, rs As Recordset, tbl As TableDef
    Dim errLoop As Error


    ' ---------------------------------------------
    ' Register ODBC database(s)
    ' ---------------------------------------------
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblODBCDataSourcesOracle")
    DoCmd.SetWarnings False
    With rs
    .MoveFirst
    While Not .EOF
    'DBEngine.RegisterDatabase "applix_prd", "Oracle73 Ver 2.5", True, _
    ' "Data Source Name = applix_prd" & _
    ' Chr(13) & "Description=applix_prd" & _
    ' Chr(13) & "SQL*Net Connnect String = applix_prd"

    ' ---------------------------------------------
    ' Link table
    ' ---------------------------------------------
    strTblName = rs("LocalTableName")
    strConn = "ODBC;"
    strConn = strConn & "DSN=" & rs("DSN") & ";"
    strConn = strConn & "APP=Microsoft Access;"
    strConn = strConn & "DATABASE=" & rs("Database") & ";"
    strConn = strConn & "UID=" & rs("UID") & ";"
    strConn = strConn & "PWD=" & rs("PWD") & ";"
    'strConn = strConn & "TABLE=" & rs("ODBCTableName") & ";"
    strConn = strConn & "LOGINTIMEOUT=10"
    If (DoesOracleTblExist(strTblName) = False) Then
    Set tbl = db.CreateTableDef(strTblName, dbAttachSavePWD, rs("ODBCTableName"), strConn)
    db.TableDefs.Append tbl
    Else
    Set tbl = db.TableDefs(strTblName)
    tbl.Connect = strConn
    tbl.RefreshLink
    End If
    rs.MoveNext
    Wend
    End With
    CreateODBCLinkedOracleTables = True
    CreateODBCLinkedOracleTables_End:
    Exit Function
    CreateODBCLinkedOracleTables_Err:
    If DBEngine.Errors.Count > 0 Then
    For Each errLoop In DBEngine.Errors
    MsgBox "Error number: " & errLoop.Number & _
    vbCr & errLoop.Description

    Next errLoop
    End If

    Resume CreateODBCLinkedOracleTables_End
    End Function

    This code refreshes links to the Oracle tables, which I found to be more efficient because it is real-time, rather than having to import them all the time. If you create your links first, then use this code in your application, it will refresh your links so they don't time out.

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    ... Thanks for the help.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    Charlotte,
    ... I vote for the easier application, that is DAO. Any suggestions on the code to import the data. As a refresher, I would normally select File, Get External Data, ODBC, Select the correct data source, and the table names become available. I would then select tables A, B, and C (as an example) and they would import into Access

    Help..... My forehead appears to be getting flattened from "placing it gently" against the wall <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    ... SUCCESS.... <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    I gave up on trying to code this (since I could not get it to work) and used the code to launch a macro which uses the Transfer Database command to get the data.

    Thanks to everyone for your help and ideas. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC, Importing Tables (A2K, SR1)

    You would use the transferdatabase method in code as well if you're using DAO. Here's a sample line of code that does it:

    DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable, "MSysObjects", _
    "USysObjectsLinkedDB"

    In this case, I was linking the MSysObjects table from another database into the current one. The strPath variable holds the full path and filename of the other database.
    Charlotte

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    Thanks Charlotte.

    Is there a way to get the SQL Statement(s) that are driven by events in a Macro? (Similar to viewing SQL for a query)
    Regards,

    Gary
    (It's been a while!)

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC, Importing Tables (A2K, SR1)

    I have no idea what you just asked. What do you mean, "get the SQL statements that are driven by events in a macro"? Are you talking about a macro or code? What SQL statements? DoCmd TransferDatabase doesn't involve SQL statements. Are you talking about something else?
    Charlotte

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    Please excuse my unclear wording. Let me try it again. Is there a way to obtain the code from each action in a Macro. For example, in a macro I used the action DoCmd TransferDatabase and filled in the Action Arguments. Now I would like to see what the code would look like since when I code it, I can't get it to run. This would allow me to see where I am making a mistake in the code.
    Regards,

    Gary
    (It's been a while!)

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    Hi Gary,
    Tools-Macro-Convert Macros to Visual Basic
    is what you're after.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC, Importing Tables (A2K, SR1)

    Rory,

    Thats exactly what I was looking for.... Thanks.
    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •