Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    246
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access ODBC question

    I need to use Access as an ODBC server only.

    To explain, I want to regularly sync a paradox table with a Palm DB and the sync conduit only works with Access.

    Two Questions:
    1. What suggestions does anyone have for the minimum install of Access needed to acomplish this?
    2. Any comments, suggestions, warnings regarding opening Paradox tables in Access?

    Thanks

  2. #2
    zxis
    Guest

    Re: Access ODBC question

    reply to part 2)

    Access's ODBC driver requires the PDOX table name to be in 8.3 format.

    Memo and BLOB fields only display and work with the characters stored in the PDOX table, not the objects themselves.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    246
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access ODBC question

    I think that I undrserstand the implications as far as BLOB fields are concerned, but, are you saying that the entire memo field does not display or work, just the part of it that is stored in the *.db file, i.e. not the contents of the *.mb file?

  4. #4
    zxis
    Guest

    Re: Access ODBC question

    Blob fields do not import or link and Memo fields only link/import up to the number of characters in the field definition. If you have a memo field with 600 chars, you only import the field definition up to 240 chars.

    For memo fields, I use the PDOX export memo method. I make the exported file name equal to the PK of the record. I have a routine that gets the filename and creates a file with the index number (from the filename) and the second field is the actual memo.

  5. #5
    zxis
    Guest

    Re: Access ODBC question

    I did not have it when I replied, but here is some code that writes memo data and a record id to an access table. I typically use the PDOX memo export method to write out memo blobs to a file with the record id as the filename, then use this routine to pull it into a temporary table in Accesss, and then use an update query to move the data over.

    PDOX filename example: 13387.txt where 13387 is the PK of the record. The Access routine will extract the id number and load the memo data. There are some extra vars I use for other routines.

    Private Sub Command0_Click()
    Dim strQuote, Response As String
    Dim MyFile, MyPath, MyName, mychar, id As String
    Dim arimport(2) As String
    Dim count As Long
    Dim rcd As Recordset, db As Database

    Set db = CurrentDb
    Set rcd = db.OpenRecordset("import", dbOpenTable)
    count = 0
    ' Returns filename with specified extension. If more than one *.ini
    ' file exists, the is returned.
    DoCmd.Hourglass True
    Close #1
    Response = " "
    MyPath = "E:testmemos" ' Set the path--14 chars long
    MyFile = "*.txt" 'File type to look for--4 chars long
    MyName = MyPath + Dir(MyPath, vbNormal) 'filename first file found
    'MyName = MyPath + MyName
    id = Mid(MyName, 15, (Len(MyName) - 18))
    'do for rest of files in directory
    'MsgBox MyName & " ; " & MyPath & " ; " & id

    Do While MyName <> MyPath ' Start the loop.
    count = 0
    Open MyName For Binary As #1 ' Open file.
    id = Mid(MyName, 15, (Len(MyName) - 18)) 'extract record id
    Do While count < LOF(1) ' Loop until end of file.
    If EOF(1) Then Exit Do
    Input #1, mychar
    Response = Trim(Response & " " & mychar) 'build up string, put space for row,
    'remove lead & end blanks
    count = Loc(1) + 1
    Loop
    Close #1 ' Close file.
    With rcd
    .AddNew 'insert new record
    !id = id
    'MsgBox id & " : " & Response
    !text = Response
    .Update
    End With
    Response = ""
    MyName = MyPath + Dir ' Get next entry.
    Loop
    rcd.Close
    DoCmd.Hourglass False
    End Sub

Posting Permissions

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