excel vba - SQL query to extract employee details from access table -


i new excel vba. have user form in trying populate names of employees amo. have database called ofc. under have table employeedetails. primary key peoplesoftid.

here structure , contents of employee table:

peoplesoftid  nameofemployee  racifid  employeeid  designation 43243309      maddala         v43309   99651823    amo 43243310      abhishek        a43301   99651824    amo 43243311      atanu           a43311   99651825    mo 43243312      rajiv           r43312   99651826    cse

this code i've written far:

dim cnn adodb.connection 'dim ado collection class dim rs adodb.recordset 'dim ado recordset class dim dbpath string dim sql string dim integer dim var 'add error handling on error goto errhandler: 'disable screen flickering. application.screenupdating = false dbpath = "e:\office_hsbc\ofc.accdb" var = "amo" set cnn = new adodb.connection ' initialise collection class variable cnn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" & dbpath cnn.open sql = "select nameofemployee employeedetails designation= '" & var & "'" set rs = new adodb.recordset 'assign memory recordset rs.open sql, cnn if rs.eof , rs.bof rs.close cnn.close 'clear memory set rs = nothing set cnn = nothing 'enable screen. application.screenupdating = true msgbox "there no records in recordset!", vbcritical, "no records" exit sub end if = 0 rs.fields.count - 1     comboamo.additem rs.fields(i).value, next rs.close cnn.close set rs = nothing set cnn = nothing msgbox "congratulation data has been imported", vbinformation, "import successful" 'error handler on error goto 0 exit sub errhandler: 'clear memory set rs = nothing set cnn = nothing msgbox "error " & err.number & " (" & err.description & ") in procedure import_data"

you need move through each record in recordset. trying read of fields single record query returns 1 field. try instead:

msgbox "there no records in recordset!", vbcritical, "no records" exit sub end if = 0 until rs.eof     comboamo.additem rs.fields("nameofemployee").value,     rs.movenext     = + 1 loop rs.close

Comments

Post a Comment

Popular posts from this blog

html - Outlook 2010 Anchor (url/address/link) -

i'm trying link specific pdf page msoutlook'10 or msword'10, #page=... anchor particularly useful , works fine elsewhere. unfortunately when click [ctr-k] in outlook or winword, links re-formatted assuming unc path. pdf file located on shared drive. using "file:" prefix links open pdf in adobe reader (outside of browser), , "http:" open inside browser. issue seem need open in browser "file:" prefix in order anchor work. know anchors work inside browser. outlook uses windows/explorer default program (based on file extension) seems open in adobe reader neglecting anchor. is aware of solution, link specific page or bookmark of shared-pdf ms outlook and/or winword link? common references: mailermailer.com/...anchor-tags-html-emails campaignmonitor.com/...anchor-links-in-email-newsletters/ stackoverflow.com/...anchor-in-pdf-document
Read more

javascript - Why does running this loop 9 times take 100x longer than running it 8 times? -

consider code: test = function() { } t = new test(); (var = 0; < 8; i++) { result = t instanceof test; } if change number of iterations 8 9 , loop take 100 times longer complete in latest version of firefox (41.0.1). tested on 2 different pcs , magic limit 8. here jsperf test used: http://jsperf.com/instanceof-8-times-vs-9-times does have idea why might happen? seems specific instanceof . not happen if else object, example check property. note: filed bugzilla bug this. jan de mooij mozilla team has posted details in bugzilla thread . here's simplistic interpretation of highly technical answers: in i < 8 case, firefox smart enough hoist result = t instanceof test; statement out of loop (according tests, doesn't seem omit altogether). in i < 9 case, apparently doesn't optimization . why? reason not clear, related fact 9 iterations threshold above function considered "hot" enough run through jit compiler. i < 8 cas...
Read more

Getting gateway time-out Rails app with Nginx + Puma running on Digital Ocean -

following this guide setup app on digital ocean droplet, not working. site not loading , it's throwing: "gateway time-out". the puma server works, since ps aux | grep puma prints: root 15878 0.0 0.4 376832 2168 ? sl 12:50 0:00 puma 2.13.4 (unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock) [20150930164940] root 15881 0.0 0.7 903676 3692 ? sl 12:50 0:06 puma: cluster worker 0: 15878 [20150930164940] root 24654 0.0 0.1 11748 916 pts/2 s+ 18:00 0:00 grep --color=auto puma my nginx config: user root; worker_processes 1; events { worker_connections 1024; } http { upstream puma { server unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock; } server { listen 80; server_name myapp.com; root /var/www/myapp/current/public; access_log /var/www/myapp/current/log/access.log; error_log /...
Read more