Solved

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

Posted on 2016-10-27
8
24 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
  • 5
  • 3
8 Comments
 
LVL 27

Accepted Solution

by:
Ark earned 500 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
 
LVL 27

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 27

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now