×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

'LIL HELP IN VB PERVASIVE TO EXCEL PROG

'LIL HELP IN VB PERVASIVE TO EXCEL PROG

'LIL HELP IN VB PERVASIVE TO EXCEL PROG

(OP)
Hey Everyone,

  I recently wrote a prog in vb to extract data from access into excel. What I really need to do is to extract live data from pervasive 2000i into excel. So, my question is....How tuff is it going to be to taylor the access to excel prog to pervasive to excel? I've been working through some trouble shooting and ran into a snag! Anyones help would be appreciated!


This is some of the code logic i've used thus far


how do i define pervasive in vb?

Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
Dim row As Long
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim statement As String
Dim col As Integer

---------------------------

Here is where I open the db and connect!

   Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=MSDASQL;" & _
        "Driver ={Pervasive ODBC Client Interface};" & _
        "SeverName=" & server & ";" & _
        "ServerName=" & server & ";" & _
        "ServerDSN=summit" & _
        co & _
        ayear & ";" & _
        "UID=Mas" & _
        "ter;PWD=AL" & _
        "EX"
     
    conn.Open
-----------------------------

Private Sub Form_Load()
Dim file_path As String

    file_path = App.Path
    If Right$(file_path, 1) <> "\" Then file_path = file_path & "\"
    txtExcelFile.Text = file_path & "empty_steve_project.xls"
    ????????????????  = file_path & "C:\Query's\111TEST.sql"
End Sub




Thanks,
JRHEIN
IS ADMIN

RE: 'LIL HELP IN VB PERVASIVE TO EXCEL PROG

I'm not sure I understand what problem you are having?  Are you trying to load an SQL file with a number of SQL statements and execute them against a PSQL database?  
You code for connecting looks good.  Does the "conn.Open" work? Or does it return an error?  
My DataXtraction tool will access an ODBC Datasource and export it to CSV (which can be opened directly in Excel).  

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: 'LIL HELP IN VB PERVASIVE TO EXCEL PROG

(OP)
HEY MIRTHEIL,
   I APPRECIATE YOUR INQUERY AND ANY ASSISTANCE YOU MIGHT HAVE ON THIS MATTER. WHAT I'M TRYING TO DO IS TO EXTRACT INFO FROM "LIVE" PERVASIVE 2000I INTO EXCEL! MY COMPANY HAS USERS THAT ARE CONSTANTLY ASKING FOR INFO THAT IS EASILY EXTRACTED BUT FOR SOME REASON THEY CAN'T WRITE OR COPY AND PASTE A SIMPLE QUERY! SO, I'M TRYING TO DEVELOP AN EASY BUTTON FOR THEM! BELOW IS THE PROGRAM I WROTE IN VB 6.0 TO EXTRACT INFO DIRECTLY FROM AN ACCESS DB TO EXCEL! NOW, I'M TRYING TO TAYLOR OR ADJUST IT TO EXTRACT INFO FROM PERVASIVE TO EXCEL. SINCE, THE DATA NEEDED IS BEING ADJUSTED AND CHANGES PERIODICALLY THERE IS A FUNDEMENTAL FLAW IN MY ORIGINAL LOGIC! I MIGHT EVEN ADJUST IT TO ACCEPT USER INPUT AN QUERY THE DATA THEN EXTRACT TO EXCEL. I AM INTERESTED IN SEEING YOUR DATA EXTRACTION TOOL AND ANY INPUT YOU MIGHT HAVE!

HERE IS MY PROG!

Option Explicit


Private Sub Form_Resize()
Dim wid As Single

    wid = ScaleWidth - txtAccessFile.Left - 120
   If wid < 120 Then wid = 120
    txtAccessFile.Width = wid
    cmdLoad.Left = (ScaleWidth - cmdLoad.Width) / 2
End Sub




Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
Dim row As Long
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim statement As String
Dim col As Integer

    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")
    
    excel_app.Visible = True

    ' Open the Excel spreadsheet.
    excel_app.Workbooks.Open FileName:=txtExcelFile.Text

    ' Check for later versions.
    If Val(excel_app.Application.Version) >= 8 Then
        Set excel_sheet = excel_app.ActiveSheet
    Else
        Set excel_sheet = excel_app
    End If

    ' Open the access database.
     Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & txtAccessFile.Text & ";" & _
        "Persist Security Info=False"
    conn.Open
    
    ' Select the data.
    Set rs = conn.Execute( _
        "SELECT * FROM query1 ORDER BY loc_no", , _
        adCmdText)
       
    ' Make the column headers.
    For col = 0 To rs.Fields.Count - 1
        excel_sheet.Cells(1, col + 1) = rs.Fields(col).Name
    Next col

    ' Extract data from the database and insert
    ' it into the spreadsheet.
    
    row = 2
    Do While Not rs.EOF
        For col = 0 To rs.Fields.Count - 1
            excel_sheet.Cells(row, col + 1) = rs.Fields(col).Value
        Next col

        row = row + 1
        rs.MoveNext
    Loop

    ' Close the database.
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing

    ' Make the header bold.
    excel_sheet.Rows(1).Font.Bold = True

    ' Make the columns autofit the data.
    excel_sheet.Range(excel_sheet.Cells(1, 1), excel_sheet.Cells(8, 6)).Select
    excel_app.Selection.Columns.AutoFit

    ' Freeze the header row so it doesn't scroll.
    excel_sheet.Rows(2).Select
    excel_app.ActiveWindow.FreezePanes = True

    ' Select the first cell.
    excel_sheet.Cells(1, 1).Select

   

    ' Close the workbook saving changes.
    excel_app.ActiveWorkbook.Close True
    excel_app.Quit

    Set excel_sheet = Nothing
    Set excel_app = Nothing

    Screen.MousePointer = vbDefault
    MsgBox "Copied " & Format$(row - 2) & " values."
End Sub

Private Sub Form_Load()
Dim file_path As String

    file_path = App.Path
    If Right$(file_path, 1) <> "\" Then file_path = file_path & "\"
    txtExcelFile.Text = file_path & "empty_steve_project.xls"
    txtAccessFile.Text = file_path & "steve_project.mdb"
End Sub


Private Sub Label2_Click()

End Sub

REGARDS,
JRHEIN
IS ADMIN

RE: 'LIL HELP IN VB PERVASIVE TO EXCEL PROG

Please no yelling (ALL CAPS).  We'll try to answer any questions you have.  
I'm still not sure where you problem is.  Are you having problems connecting?  If so, what error are you seeing?  
As far as my DataXtraction tool, a trial version is available on my website.  

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close