'LIL HELP IN VB PERVASIVE TO EXCEL PROG

[JRHEIN]

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

 

[mirtheil]

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

 

[JRHEIN]

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

 

[mirtheil]

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