SQL command for ADODB Connection

[vijay.vizzu]

Hi..all

I am working on a project for my users who can create purchase orders by their own. Below is my quick summary of my project.

PO Application tool, in which i am using ADODB connection to fetch details from another excel workbook which is situated at my desktop. I have created some user forms to interact with the files.

Now my problem is.. i have written some code to get details from the other workbook. But its not working, i can't able to find out the way to resolve it as i am new to VBA programming and wants to improve my skills.

Please have a look on frm_PO_Make Form. Please suggest, how can i get details of vendor like if user put vendor code, the vendor details should be displayed on assigned labels like vendor name, address, state and pincode.

Please help me .. to resolve...

 

[vijay.vizzu]

I have solved this.... by doing some research on internet and reading some blogs..

thank you so much...

 

[Deepak]

Wow! It good to know that you have get it resolved. So, Pls share the same as that might help someone!!!

 

[vijay.vizzu]

Of course deepak, i will share definately...

The main problem is in my SQL statement which i written to fetch details from other workbook (vendor_Db).

 Intial code :strSQL = "SELECT * FROM [Vend_DB_SJP$Table1] WHERE Vendor_Code = " & VCd & ";"

Modified Code: strSQL = "SELECT * FROM [Vend_DB_SJP$A1:H1000] WHERE Vendor_Code = " & VCd & ";"

Intial code i refered with table1, due to this record set can't able to open. But when i changed to range reference it works..

I just to know from others that, can it be possible to use Table reference in SQL statement. If yes, then please how to achieve this

 

[Deepak]

This might help.

http://stackoverflow.com/questions/...-excel-table-within-a-workbook-with-vba-macro

http://stackoverflow.com/questions/...ements-on-a-named-range-within-an-excel-sheet