VBA to get data from database.

[DHolcombe]

hi,

I am trying to use an excel vba program to get data from a database and then use this data in my program. But i have no clue about where to start.

Can someone help? I know this is kinda vague but i am not sure exactly what i need to know. Not in any of the books that i have on excel vba.

 

[lenze]

Depending on how complicated your requirements are, I would really suggest you seriously consider using MSQuery. When I worked for the state of Montana, I used it daily with a fairly large Oracle database (2500 tables) and NEVER had to write any code to get the results required. Hint: Get by using the wizard and go into design mode for maximum options.

lenze

 

[xenou]

Here's a link showing the processing of setting up a DSN (hopefully its enough to get you started). http://www.webcheatsheet.com/asp/dsn.php You will see some of the same options from Exel data connections tab also - once you set up the DSN (Data Source Name) you can choose it as a data source and get right to the query designer. Maybe someone in your IT staff (?) can help - once it's set up you don't need to do it again.

I agree that using MSQuery is very useful. I've also used it to connect to a corporate SQL Server database and had no problems - no code to write by hand, and easy to implement.

If you're trying to connect to an enterprise client-server database (such as Oracle or SQL Server), you will need to have the proper privileges to connect in the first place - either through Windows authentication, which means that Windows will recognize you, or via a username/password. You might need to know the name of the server as well, where the database is.

 

[DHolcombe]

Thanks for the post.

I had a basic question. You had a comment on one of your lines of code that read

'<--- Update these details, change the providor if needed'

It was for the following line of code:

Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=DATABASE;USER ID=User1;PASSWORD=Pwd1"

How do i find out these details?

Little background:
I installed Microsoft sql server 2005 at the request of one of the IT guys. He then sent me a copy of the database i would be accessing and told me to write my code and practice with this before using the real database. So i restored the data base. But i have no idea as to the details for PROVIDER, DATASOURE, USERID, OR PASSWORD.

Any ideas?

 

[Blade Hunter]

conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;<WBR>PWD=password;DATABASE=mydatabase "

Provider is as above.

Source is the server name, so if you called it MyMSSQLServer then that is what you put in there.

UID and PWD are the connection details. When you connect you should put in a username and password, these are what you need to enter.

Database is the database to connect to on the server so on the MyMSSQLServer you may have 4 different DB's, lets say one of them is called MYDB, that is what you put in there.

Your DB guys should be able to help you with this.

 

[DHolcombe]

Hi,

I took your advice and found some code to verify my connection (see below):


Sub Macro1()
Dim Conn As New ADODB.Connection
Set Conn = New ADODB.Connection


Conn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=SAUK\SQLEXPRESS;Trusted_Connection=True;DATABASE=Timeclock"


' Find out if the attempt to connect worked.
If Conn.State = adStateOpen Then
MsgBox "Welcome to Timeclock!"
Else
MsgBox "Sorry. No Timeclock today."
End If

' Close the connection.
Conn.Close

End Sub



Unfortunately, i get a compile error ...User-defined type not defined. The error seems to be occuring on the first line "Dim Conn As New ADODB.Connection"

Any ideas?

 

[DHolcombe]

One thing i wanted to mention that may or may not be of concern. I had to re-install my system about 1 month ago and i had to re-install excel as well. What i am wondering is do you think i need some "add-in" that is not installed? Could this be causing the problem?

Just a thought. I really dont know why i am getting the compile error. It just seems unlikely to me that you would give me a command that issues a compile error. You seem to really know what your doing so this is why i thought maybe the error should be on my end.

 

[DHolcombe]

Ok....i found the compiler error problem.

Apparently i had to turn on

Microsoft ActiveX Data Objects 2.0 Library

It took me a while to figure out how to do this.

It was only located in the VBA screen in excel under Tools/References.

Now i try to run the code (show previous) and i get a run-time error:

"Runtime error '-2147467259 (80004005)'
Login Failed for user 'David'. Thi user is not associated with a trusted SQL server connection"

So basically it is getting "stuck" at the connection to the database........which is what i was worried about.

Any ideas?

I have only connected to my database using "windows authentication" method. There is an SQL method but that requires a password.........i dont recall setting one up when i installed sql2005.

Any ideas?

 

[xenou]

You can try creating a datalink file and see if it helps you get a successful connection (simply save an empty text file on your desktop with the extension .udl, then open it and pick your database, driver, etc. If you connect successfully, open the .udl file with notepad to view the connection string). Instructions are here: http://msdn.microsoft.com/en-us/library/e38h511e(VS.71).aspx

Normally, if you installed the SQL Server Express database then you should have access to it as a trusted user. It also has to be running - so if the database doesn't start automatically when you start windows, it may need to be started.

Again, just to re-iterate, using a DSN and MSQuery from within Excel may in the end save you a lot of time writing code. I'm personally a fan of ADO in many ways myself. The choice is really yours anway.

ξ

 

[DHolcombe]

Hi xenu,

I am not sure i understand why i need to create this .udbl file.

To be honest i pretty frustrated. I am kinda out of my element with this whole thing. But i am trying to do this for a family friend and they really need it so.......i will keep trying.

Whats interesting is that when i try and run the vba from excel it says "David" does not have login privaleges or something like that......see previous post. Whats funny is i never told vba or sql 2005 express about the name "David".

I just dont get it. Not sure what to do to be honest. Not even sure what question to ask.

 

[DHolcombe]

When you say the database has to be running..........what exactly does this mean. Do you mean that Microsoft SQL Server 2005 has to be running. If so then i opened up the application. Highlighed my database. Then tried to run excel vba code to verify connection. Still get same error.