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.

 

[DHolcombe]

hi xenu,

i did as you said and here is what was in the notepad file

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;User ID=Sauk\David;Data Source=dBASE Files


what does all this mean?

 

[DHolcombe]

hi xenou,

How do you know if i connected successfully or not?

 

[ndendrinos]

Out of curiosity the title says: VBA to get data from database
Yet no one has asked "what for?"
If DHolcombe would explain what the desired end result is then maybe the procedure to meet this result might be simpler? (i.e. The database could be a separate sheet within the same workbook ?)
As I say ... just out of curiosity...

 

[xenou]

Hmm, well in principle that is the connection string and you only need to give it to ADO "as is" (when you open the connection).

Now, its darned interesting that you have dbase files in it ... Which driver did you select? Note again, when using a UDL link there is a button to click for "test connection". If you click it, you'll find out right away if the connection succeeds or fails. Of course, you want to succeed - and only then take a peek at the text with notepad.

EDIT: note, probably on the provider tab, you want to start by selecting Microsoft OLE DB Provider for SQL Server (for use with SQL Server or SQL Server Express)

ξ

 

[DHolcombe]

I am trying to write vba code within excel to go out and retrieve data from a database. The database is a time clock system. I am trying to do this within an existing excel macro. Why do it within macro. Well, let me back up a min. I am writing this for a friends family business (for free......learning alot). The business is trying to track things like hours worked by employees and such. This information is used within excel to track certain metrics. Right now hours are input "by hand". Since they hafve this expensive timeclock system that is on the network they asked if i could make it so hours are not inputed by hand but somehow automate this.

I hope that explains what i am trying to do and why.

 

[DHolcombe]

XENOU,

I tried what you suggested again this morning...........and it seemed to work. I think i did select the SQL server. But i pressed the test connection button and it said it passed. Here are the contents of the file

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Timeclock;Data Source=SAUK\SQLEXPRESS


To get it to work here is what i remember doing:
1) Started Microsoft SQL server manager 2005
2) Clicked on the ".udl" file
3) On the provider page i selected the Microsoft OLD DB provider for SQL server
4) On the connection tab. Clicked on the refresh button.
5) On the connection tab. Typed in server name because it did not appear in drop down.
6) On the connection tab. I selected "Windows NT security" radio button
7) On the connection tab. I then typed in the database name.

I then pressed the "Test connection" button and it said it was successful.

So i think i connected. Now what? Is whats in the file that i pasted above what i need to put into the excel macro?

 

[DHolcombe]

Well, i tried copying and pasting the data from the notepad file into the excel macro .........AND IT WORKED!!!!!!!!!!!! I have no idea why. Guess i did not have all the relevant info in my connection string.

Now, here is another question i would like to get your input on.

As i said in one of my previous posts i am currently doing all this work on my computer at home. At some point i am going to be going to my friends family business and trying the excel macro there. I have a feeling i am going to run into the same issues once i try and run the macro at their place. Any suggestions on how to figure out what i need to do once i try and port macro to their site?

Thanks again.

 

[DHolcombe]

I left this out in previous post.

Right now i selected the "windows NT" security.......to connect.........how will i know if they use this on their network or if they require sql login/password type stuff.

AGain, just to re-iterate.........i am a newbie newbie when it comes to networks.

 

[DHolcombe]

I was also able to connect by inputting login and password (ie...not using the NT security setting). But when i added login and password the connection string did not include the password so i had to add this into the vba code and IT WORKED! I can now connect to database.

 

[DHolcombe]

Well, now that i can connect my next hurdle is to run the sql query that i havel.

BLADE HUNTER:
I have a question about the code you sent.

sqlText = "SELECT * FROM SYSOBJECTS" '<--- Put your SQL Query in here


I am not sure i understand or how to input SQL "here"

can you put some dummy SQL code in as an example to show me.

If anyone else knows what BladeHunter was referring to that is fine as well.

Thanks again for all that have helped so far........could NOT have done this without you guys.