Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    9

    Unanswered: Converting Base64 to Binary when transforming SQL to Access

    Hi All,

    I am trying to transform an SQL database to a Microsoft Access database, however my original database has base64 text strings stored in it, which are longer than the limit for an Access memo. Consequently, the transformation wizard shortens the strings when it transforms the database. To get around this, I was hoping to be able to convert the base64 strings back to binary and store them as OLE Objects in the Access database. Does anyone know an easy way (or even a hard way) to do this? Or is there another way to get around the problem?

    Cheers,
    Clive

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    you can use ADO 2.5 and get BLOB out of your SQL.... into Access ....

    http://support.microsoft.com/default...;EN-US;q258038


    jiri

  3. #3
    Join Date
    Jan 2003
    Posts
    9
    Originally posted by playernovis
    you can use ADO 2.5 and get BLOB out of your SQL.... into Access ....

    http://support.microsoft.com/default...;EN-US;q258038


    jiri
    Hi Jiri,

    I have read the link you suggested, but don't understand exactly how ADO 2.5 can be used to handle the transformation from a base64 string to binary. It says that ADO 2.5 can be used to insert blob data or retrieve blob data from an SQL database, but I want to take text data from an SQL database, convert it to binary and, store it as blob data in and Access database. Can ADO still be used for this? Any idea how?

    Cheers,
    Clive

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    I'm sure that there are more solutions....

    1. Import all fields exept TEXT

    2. Create OLE field or create new MDB file a store OLE and ID field there (it depends on the size of the table, generally say OLE is not good friend...).

    3. then I'd go one by one and create TXT file from your TEXT field, you can use ADO for reading the field and WRITE VBA function for creating text file, then read the file and stick it into OLE field, you can use DAO or ADO BLOB.

    if your table is really big, consider to store TEXT field as files and one directory.


    if you need some samples, let me know....


    jiri

  5. #5
    Join Date
    Jan 2003
    Posts
    9
    Originally posted by playernovis
    I'm sure that there are more solutions....

    1. Import all fields exept TEXT

    2. Create OLE field or create new MDB file a store OLE and ID field there (it depends on the size of the table, generally say OLE is not good friend...).

    3. then I'd go one by one and create TXT file from your TEXT field, you can use ADO for reading the field and WRITE VBA function for creating text file, then read the file and stick it into OLE field, you can use DAO or ADO BLOB.

    if your table is really big, consider to store TEXT field as files and one directory.


    if you need some samples, let me know....


    jiri
    Thanks.

    I am going to have a go at the text file idea. With luck I should hopefully get it working today, but any samples would be greatly appreciated.

    Thanks again for your help,

    Clive.

  6. #6
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    :-)

    I tried it for ope page text and it worked just fine.

    jiri



    Sub writeTEXT()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;data Source=jiri; Initial Catalog=test1;User Id=sa;Password=mypassword"

    Set rs = New ADODB.Recordset
    'my table is called TEMP and my TEXT field is called TEST1
    rs.Open "Select * from temp", cn, adOpenKeyset, adLockOptimistic

    'cycle through all records
    Do While Not rs.EOF
    'read TEXT and save it to the file (you can use rowID from your table as file name)
    Open "c:\file.txt" For Output As #1
    Print #1, rs.Fields("test1").Value
    Close #1 ' Close file.
    'use BLOB sample from Microsoft web site or use DAO and save text file to OLE
    ' ...
    rs.MoveNext
    Loop

    rs.Close
    cn.Close
    End Sub

  7. #7
    Join Date
    Jan 2003
    Posts
    9
    Thanks for all your help. It is very much appreciated.

    Clive

Posting Permissions

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