Excel 2010 VBA Tutorial 71 - ADODB - Database Connections

[placebojunior]

Congratulations BRO, amazing channel, you are master.

[CoolBlockDude]

For those of you in the future trying to connect to a MySQL database...


Public Function connectDatabase()
Set DBCONT =

Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim sConn As String

Server_Name = "serverName"
Database_Name = "databaseName"
User_ID = "userIDName"
Password = "password"

sConn = "Driver={MySQL ODBC 5.1 Driver};Server=" & _
Server_Name & ";Database=" & Database_Name & _
";UID=" & User_ID & ";PWD=" & Password & ";Option=3;"

DBCONT.Open sConn
DBCONT.cursorlocation = 3

End Function

[sivakumar-iigr]

Hi Matt,

Awesome Tutorial series..

I just had one doubt while you were establishing Database connections.
You have used a .accdb file during this process.
So what if we are working in a production/Development environment where we login to a DB through Port, Host id and SIDs, How can we establish a connection in that case?

Looking forward to your reply

Cheers

[slinky]

For anyone wondering about the closeDatabase line, you're not insane, it's in the wrong place. It will only ever get called when there is an error with both connections.

[bharaniswati]

Hi Matt, really helpful video.

I have a doubt. If error goto 0 disables the error-handler above, why was DBCONT set =nothing?

[PolitPiotr]

great help
1 issue though: it throws a "Syntax error in FROM clause" if you have spaces in the file path. How to fix that? Some people suggest adding double quotation marks ("") before and after the string, but it does not work for me. Any advice massively appreciated.

[musthagmohamed]

Thank you very much for the tutorial. I have been learning a lot from your tutorials. I am having a tiny issue. after opening "Tutorial 71.xlsm", when I run VBA in your excel file "Tutorial 71.xlsm" it gives an error Run time error with description "Method 'Open' of object '_Connection' failed". Please help me. I am using windows 10 and Office 2013.

[ShavitTube]

Awesome tutorial series!
Would you mind showing us what is the connection string if we want to connect to Excel (xlsb) file with a password?

[Hythloday]

What does a string DB path connection look like for 'computer1's file, say in C:\DATABASES, accessed on network by 'computer2' ? is it
StrDBPath = ... I'm just noticing you used front slashes, i thought it was back slashes ?

[MrRossss]

Hi. Just to let you know. I couldn't register on your site as it said to enter the 3rd word of the sentence but there was no sentence showing

[B]

hi matt great Tutorial but i want to provide a connection string depending upon the users office version Can u help me to do so

[gioxc]

Hello Matt. Great Videos. I watched almost all of them.
My question is if the database has to be necessay in access. I use HeidiSql to question the database. It is MySQL database. Do you have some references for this?

Thank you

[CathyX]

Hi Matt - Thanks for these videos - not just explaining the functions, but your advice on using them too, especially the CreateObject tip. They are helping me simplify some horrible spaghetti & meatball code I created while learning VBA. Just a question: You made these routines functions, but would they work just as well as subs?

[DimitriBoyarski]

04:30 Pretty sure it saves references with the file.

[daromc]

do you know how is to do the connection with outlook 365 ?

[alanelston]

Hi,  
THANKS a lot for doing your Video series. I really appreciate it. I am an old Practical Physicist, That hat to “wake-up” very quickly to computers a couple of years ago for an urgent private project.. My project was / is taking too long. (Big Excel files with lots of data)
So I am hoping to speed it up a bit by getting into programming. A good solution for me looks like using a combination of Excel and programming somehow in reading, writing and sorting simple but very large text files. 
Your videos 71 – 93 are very close to wot I am in to. But as a computer beginner (or Rather late starter!), I can not quite get to convert them to work with Simple Text files (.txt) rather than ACCESS Files. I am also learning by trying to answer Threads on a Forum. In one I used something close to you techniques that almost does with a text file wot your first couple do with an ACCESS File. Here is the Thread.


And here is the code again

Sub GetCSVtxtDataADOdb2() 'Using Microsoft's ActiveX Data Objects database to read text file to VBA
' This requires that you add a reference
' Microsoft Active Data Objects to support the ADO code.
' The idea is to create an ADO connection to the csv (txt) files folder,
' then select the columns you want using a standard SQL statement in
' an ADO Recordset to link to the actual file.
' So you Need to reference the Microsoft ActiveX Data Objects 2.5 Library (called early binding)
'        Tools>>References>>then check Microsoft ActiveX Data Objects 2.5 Library
'  ..Or
'Dim DBcnn As ADODB.Connection '  
'Set DBcnn = New ADODB.Connection
'
' The next two lines are an alternative called Late binding.
Dim DBcnn As Object
Set DBcnn =

Dim TextdtaPathstr As String 'For a text file, Data Source is the folder, not the file
Let TextdtaPathstr = ThisWorkbook.Path
Let DBcnn.Provider = "Microsoft.Jet.OLEDB.4.0"
Let DBcnn.ConnectionString = "Data Source=" & TextdtaPathstr & ";" & "Extended
DBcnn.Open ' So now you are "connected" or "pluged in" or "the tap is turned on!" and stuff can be got!

'Set up Excel File for recieving data
Dim nextRow As Integer
Let nextRow = 2
ActiveSheet.Cells.Clear

'This bit gets wot can be now got, for example a record set. You need to have and Know what your headings are.
Dim DBRecordset As ADODB.Recordset
Set DBRecordset = New ADODB.Recordset
Dim TextdtaFilename As String
Let TextdtaFilename = "SayreTextDataCSV_XXX"
DBRecordset.Open "SELECT hostname, hostuid, lineid, Disk_XXX, Status, Size_GB, Free_GB, Dyn, Gpt FROM [" & TextdtaFilename & ".txt]", DBcnn ' Note: You can change   hostname, hostuid, lineid, Disk_XXX, Status, Size_GB, Free_GB, Dyn, Gpt   with   *
DBRecordset.MoveFirst ' Moveto next "Record". For the case of a text file it is the next Row / line. But it is more compliceted with ACCESS etc.!

' This bit copies the selected records, ' starting at the row below the last used row in the Sheet.
ActiveSheet.Cells(nextRow, 1).CopyFromRecordset DBRecordset

'And then finally close and dispose of the connection and recordset. -Good practice to close / shut off all these things!
DBRecordset.Close
DBcnn.Close
Set DBRecordset = Nothing
Set DBcnn = Nothing
End Sub 'GetCSVtxtDataADOdb2()


I wanted to ask if there was any chance of you doing some videos along the lines of 71- 93 but applied to Text Files?

Thanks again for all your Videos. I have watched them all a few times now.
Alan Elston
Bavaria
Germany

P.s I have seen and learnt a lot from your later Videos on reading text Files. But I would like to learn well the ADOdb Streaming stuff just now.

[MrGargmay]

What if I need to connect to a external database and have username and password to login to the same. 

[ghouseuddin]

hi Matt,
Provider info from where we can get?

[johna]

how to connect excel and i need a immediate reply

[avinashmishr]

COULD U REMOVE POLITICAL ADD IN YOUTUBE. IT IS SO IRRITATING AND HARRASSING.