Vba code to Validate

[jamesexcel1970]

Hello Everybody.

Working with some new assignment, project is all about from excel vba sheet need to validate data with the tables existing in Sql server.

1) From excel first row should be concatenated not in excel workbook rather in the code and should go to SQL server table 1 and check if the combination existing else check in the table 2..if existing msgbox "Existing" else "Not existing" .

Quick Screenshot



Attached Sample file.

 

[Chihiro]

Not enough detail to really help you out.

Are you at least able to pull data from SQL using ADO?
What columns are present in SQL Table and in what format?

You may find it much easier to work with PowerQuery for this sort of validation.

Major advantage of PowerQuery.
1. GUI control over data set imported
2. Each transformation step is recorded and you can jump between steps to trouble shoot. Or even remove steps.
3. Less finicky with data type than ADO.

Only major disadvantage PowerQuery has is that it can't update record in SQL. But from your description, this isn't required.

 

[jamesexcel1970]

Hello Chihiro

Thanks for responding.

i havnt started with any coding...Am not that expertise..Minimum code i can manage as just taking baby steps with this forum.

I have same table format in SQL server like EXCEL as per screenshot.

That's how i defined so that coding should not have any issue in validating.

Cheers!

 

[Chihiro]

Version of Excel and SQL?

 

[jamesexcel1970]

Hello

Version details

Excel 2013 and SQL server 2012 management Studio or 2014.

Please advise do i need to go for 2012 or 2014 for my requirment.

This is yet to install.

Cheers!

 

[Chihiro]

Ok, so PowerQuery isn't available on default (unless you have Stand-Alone Excel or ProPlus).

Use code found in link below to test if you are at least able to query database.
http://stackoverflow.com/questions/27385245/using-excel-vba-to-run-sql-query

Study connection string in link below as you may need to change it to suite your server setting etc.
https://www.connectionstrings.com/sql-server-2012/

If not, you may not have sufficient privileges and/or correct setting in SQL server.

 

[jamesexcel1970]

Hello Chihiro.

Thanks for the link which i was going through since last week.

Connecting to SQL server with Excel is found.

But taking excel 3 cells as concatenation and checking with the table which is exiting in database and need to validate.

Example : First row

100 AA 50000 to be concatenation in a variable as i am not supposed to do in excel should be done with code and validated with the tables and in tables will have a column with same concatenation

Cheers!

 

[Chihiro]

Why concatenate?

If you can get data from SQL using ADO.

Just construct SQL statement using each value in "WHERE" clause, if there is recordset returned with data, then you know it's valid.

 

[jamesexcel1970]

Hello Chihiro.

Yes we can think on this...iam trying install sql server 2012...i will start writing code and will take your help.

at age of 50 trying to safeguard my job for my family and learnt lot of things from this forum.. and trying to survive...

But one thing learned from this forum...first try and then post by Monty.



Thanks for everything.

Cheers!