Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Looking for VB6 code to read SQL table export it to ascii

Posted on 2016-10-27
8
Medium Priority
?
89 Views
Last Modified: 2016-10-27
We need help in exporting a table to ascii.  The code is in an old protect in VB6.

We have:
cnn.Open "Provider=SQLOLEDB; " & _
          "Initial Catalog=" & lCompanyDB & "; " & _
          "Data Source=" & SqlServerName & "; " & _
          "integrated security=SSPI; persist security info=True;"
  
  Set rstVendor = New ADODB.Recordset
  ssql = "SELECT * from TABLE-NAME"
  rstVendor.Open ssql, cnn, adOpenKeyset, adLockOptimistic

Open in new window


We want to export the result of that query to a file.

Whats the best way to go about it?
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 28

Accepted Solution

by:
Ark earned 2000 total points
ID: 41863233
https://msdn.microsoft.com/en-us/library/ms676975(v=vs.85).aspx
Dim tableContent As String
Dim columnDelim As String, rowDelim As String, nullValue As String
columnDelim = ",": rowDelim = vbCrLf: nullValue = "NULL"
tableContent = recordset.GetString(adClipString, ,columnDelim, rowDelim, nullValue)

Open in new window

0
 

Author Comment

by:rayluvs
ID: 41863243
Ok have the contents in tableContent.  How do we export it to ascii?
0
 

Assisted Solution

by:rayluvs
rayluvs earned 0 total points
ID: 41863261
Found it:

Open "c:\p.txt" For Output As #1
Print #1, tableContent
Close #1

Open in new window


But what if the file thousand records? Is there a better way?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:Ark
ID: 41863288
AFAIK this is the fastest way. ADO perform all reading and building string operations. Another way is using 2D array:
recordsArray = recordest.GetRows
For Excel VBA you can use  Range.CopyFromRecordset Method (Excel)
0
 

Author Comment

by:rayluvs
ID: 41863336
Can you provide an example of 'recordsArray ' based on our script below?

cnn.Open "Provider=SQLOLEDB; " & _
          "Initial Catalog=" & lCompanyDB & "; " & _
          "Data Source=" & SqlServerName & "; " & _
          "integrated security=SSPI; persist security info=True;"
  
  Set rstVendor = New ADODB.Recordset
  ssql = "SELECT * from TABLE-NAME"
  rstVendor.Open ssql, cnn, adOpenKeyset, adLockOptimistic

Open in new window

0
 
LVL 28

Expert Comment

by:Ark
ID: 41863341
dim values as Variant
values = rstVendor.GetRows
Dim txt As String
For rowNum = LBound(values, 2) To UBound(values, 2)
    For colNum = LBound(values, 1) To UBound(values, 1)
        txt = txt & values(colNum, rowNum) & ", "
    Next c
    txt = Left$(txt, Len(txt) - 1) & vbCrLf
Next r

Open in new window

Note that this code is much slower then GetString - VB6 string operations as slow. But in this case you can manipulate table's data and save data as Binary using
Put #1, values

Open in new window

Furthermore, you can get data only for cpecific column(s)
values = recordset.GetRows(, , columnName)
'or
values = recordset.GetRows(, , Array("columnName_1", "columnName_2", "columnName_5"))

Open in new window

0
 

Author Comment

by:rayluvs
ID: 41863346
Thanx!
0
 

Author Closing Comment

by:rayluvs
ID: 41863347
We chose our entry as assisted if a member, like us, forgot at the time.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question