Import Excel Data into SQL using VB6

Question

I'm completely new to Visual Basic and I now need to take a program that is entirely constructed in VB6 and add the ability to import a large set of records in bulk from an excel file into the current SQL Database. all of the examples I have found online are confusing and require hard-coding of the file name (ex. Using document As New Spreadsheet() document.LoadFromFile("SimpleReport.xls")) yet this needs to be called by a user anytime they get a new set of records so I need the excel file name to be specified at time of import.

How do I import from excel to SQL using VB6? Can I make a variable for the excel filename or does the string value of the filename have to be hard coded? If I can make a variable can/should I add set and get to it in order to specify the filename? Thanks

LoadFromFile() takes a string as an argument, why are you assuming it is required to be hardcoded? See stackoverflow.com/questions/1085436/… for how to get a file name from the user. — Paul Abbott, Commented Jan 20, 2015 at 18:42
Ok, the customer now wants to do this saving the excel files to CSV which will be helpful since my knowledge of VB6 is so cursory. This form code linked should still be basically the same except for the file type so it is still very helpful. I now just need to figure out verifying that new user Ids imported don't mach existing and all users imported are marked as paid in the database. I appreciate your help — Davidp04, Commented Jan 20, 2015 at 19:55

granadaCoder · Accepted Answer · 2015-01-20 19:12:20Z

1

With a 32 bit Machine (O/S):

Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\test\myfile.xls;" & _
    "Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
    "Server=<server>;Database=<database>;" & _
    "UID=<user>;PWD=<password>].MyTable " & _
    "FROM [MySheet$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

Microsoft KB : 321686 has more ideas.

answered Jan 20, 2015 at 19:12

granadaCoder

28k11 gold badges122 silver badges165 bronze badges

Do I need to use ADO and Jet to upload the records from VB6 to SQL? I guess I get confused about why a database engine is needed to import data into an existing database, Is it making the code work like a form? I really get confused by some things in VB6. Thank you for the help
– Davidp04
Commented Jan 20, 2015 at 20:00
VB6 by itself doesn't know how to talk to a database. You must use some data-library. I think the progression was DAO, the RDO, then ADO. But you gotta piggy back on some mdac technology. Here is an article. msdn.microsoft.com/en-us/library/aa231216%28v=vs.60%29.aspx
– granadaCoder
Commented Jan 20, 2015 at 20:04
Find the KB I mention for other ideas.
– granadaCoder
Commented Jan 20, 2015 at 20:05
1

PS VB6 is an ancient technology. Move away from it as soon as possible.
– granadaCoder
Commented Jan 20, 2015 at 20:06
If this answer (or any answer) is helpful, you should upvote it. If the answer satisfies or leads you to having your issue resolved, you should mark the answer as "The Answer".
– granadaCoder
Commented Jan 20, 2015 at 20:29

Add a comment |

Collectives™ on Stack Overflow

Import Excel Data into SQL using VB6

1 Answer 1

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Linked

Related