Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19

    ADO connection and Recordset

    Dears access experts!


    I came across here with hope to learn and revise...

    I am in need of help about,how to create ADO connection with sql server and than execute a sql query on access forms where security type is sql security.,

    Also how to use ADO recordset on query...

    Please guide me with Vba code and procedures to how to create ADO connection to sql so whenever I need to open access,access don't require credentials everytime...

    I'm new to ADO connection things...I've read Doug steel method but I couldn't understand...

    I came here to get help from you experts...I hope I can get help accordingly...

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Ado is for apps that don't use a database.
    access IS a database with native connections. Can't you just link the SQL table and run a query?

  3. #3
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19
    Thanks for your reply ranman...

    Actually I don'thave access/privilege to native client....


    I linked table as ODBC and I've pass through query on one of my Form where control source of form is thst query ...
    But this approach is too slow...
    My query have more than 1 lac rows...


    When searching Form with filter on property it takes time to search...
    Can you please teach me how to make recordset in Vb than search for the record via Vba code not by filter on property

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Pass-thru queries are the fastest way to get data, and filtering will bring back a smaller recordset and will be quicker.

    Before running the PT query, change its SQL string to include the filter.

    Code:
    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("qryname")
    qdf.SQL="SELECT ..... WHERE fieldname=" & formfieldname
    
    Me.Requery

  5. #5
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by aytee111 View Post
    Pass-thru queries are the fastest way to get data, and filtering will bring back a smaller recordset and will be quicker.

    Before running the PT query, change its SQL string to include the filter.

    Code:
    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("qryname")
    qdf.SQL="SELECT ..... WHERE fieldname=" & formfieldname
    
    Me.Requery
    Thanks for your time and reply dear..

    Sorry I'm new to SQL please don't mind...

    I'm searching or filtering pass through query via Textbox named txtsearch while on click event...
    In above code you mentioned where field name I couldn't understand that...

    One more thing this form which I'm using for Search is bound to built-in navigation form...

    So searching for record from that pass through query in navigation forms the above would remain same or any changes or reference required?

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    - you have a pass-thru query
    - you want it to show 1 or more records in your search form
    - you enter a search value (txtsearch)
    - change the pass-thru SQL to include a criteria - SQL uses "WHERE" to define criteria

    If you are unsure of the syntax then you must go to the source, in this case SQL Server, create the query there and get it working, then recreate it in Access.

  7. #7
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by aytee111 View Post
    - you have a pass-thru query
    - you want it to show 1 or more records in your search form
    - you enter a search value (txtsearch)
    - change the pass-thru SQL to include a criteria - SQL uses "WHERE" to define criteria

    If you are unsure of the syntax then you must go to the source, in this case SQL Server, create the query there and get it working, then recreate it in Access.
    Yes that pass through query is control source of form where all the 1 lac rows loaded because I set up that to Form control....
    Actually the issue is I don't have privilege to sql only my tables are linked via ODBC to SQL...
    And the IT guy don't know about access he suggested me to create ADODBconnection string...and I don't know how to do...

    Instead that I created pass through query...which is opening 1 lac rows within 4 seconds...fast enough...
    But we need to find or search as below
    -via Textbox named txtsearch
    -this Textbox is before set up to filter property...now it takes 2 or 3 minutes to filter that Query
    -yes I enter value in txtsearch control on form which is unbound but search on click eventof button
    -only issue is searching or finding records from that stored query

    How can I i setup txtsearch when I click on button to find results from that Query..my form is on navigation form

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Follow instructions in post #2, change as required.

  9. #9
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by aytee111 View Post
    Follow instructions in post #2, change as required.
    Sorry but I only need to search that Query on form...
    Can you please teach me how to search?

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Post your SQL. Also what is the field name that matches txtsearch.

  11. #11
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by aytee111 View Post
    Post your SQL. Also what is the field name that matches txtsearch.
    I'll post tomorrow...my database is in laptop but it's really making me...
    Thank you dear...
    I hope you would solve...

  12. #12
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by aytee111 View Post
    Post your SQL. Also what is the field name that matches txtsearch.
    Dear aytee111,

    Please see the below sql pass through query which I'm using,for searching in txtsearch field name is employee number...

    Code:
    SELECT AX.EmployeeNumber, AX.EmployeeName, Active.ResidentName, AX.BasicSalary, AX.NetSalary, AX.Region, AX.LaborGroup, AX.Gender,
    
    AX.StartDate, AX.ContractEndDate, AX.Passport, AX.Iqama, AX.StatusEng, AX.SubStatusEng, AX.LocationDescription, AX.ProfessionEnglish,
    
    AX.Nationality, AX.ClientName, AX.CustomerContractNumber, AX.Religion, Active.PassportExpiryDateHijri,
    
    CASE WHEN PassportExpiryDateHijri<> ''
    
    THEN RIGHT(PassportExpiryDateHijri,2)+'/'+ substring(PassportExpiryDateHijri,5,2)+'/' +LEFT(PassportExpiryDateHijri,4) else PassportExpiryDateHijri end AS [Passport Expiry],
    
    Active.IqamaExpiryDateHijri,
    
    CASE WHEN IqamaExpiryDateHijri<>''
    
    then RIGHT(IqamaExpiryDateHijri,2)+'/'+ substring(IqamaExpiryDateHijri,5,2)+'/' +LEFT(IqamaExpiryDateHijri,4) else IqamaExpiryDateHijri end AS [Iqama Expiry],
    
    Active.ActiveStatus,
    
    ERVisa.ERVisaIssuanceHijri,
    
    ERVisa.ERVisaExpiryHijri,
    
    ERVisa.VisaType,
    
    ERVisa.InOutKingdom,
    
    Escaped.Status,
    
    FEVisa.FEVisaIssuanceDate,
    
    FEVisa.FEVisaTravelBeforeDate,
    
    FEVisa.FEVisaStatus,
    
    DL.DrivinglicenseType,
    
    DL.DLIssuanceDateHijri,
    
    DL.DLExpiryDateHijri,
    
    TermOKSA.Status,
    
    case when [ContractEndDate]-[StartDate]<=0 then 'Terminated'
    
    when [ContractEndDate]-[StartDate]>=0 then convert(int,getdate()-[StartDate])  else '' end AS [Working Days],
    
    case when datediff(DAY,ContractEndDate,getdate())<=0 then 'Terminated From Company' 
    
    when datediff(DAY,ContractEndDate,getDate())>=0 then convert(varchar(10),datediff(DAY,ContractEndDate,getDate()))
    
    else '' end AS [Contract Duration]
    
    FROM AXNew AX
    
    LEFT JOIN ActiveNew Active ON AX.Iqama = Active.IqamaNumber
    
    LEFT JOIN DLNew  DL ON AX.Iqama = DL.IqamaNumber
    
    LEFT JOIN ERVisaNew ERVisa ON AX.Iqama = ERVisa.IqamaNumber
    
    LEFT JOIN EscapedNew Escaped ON AX.Iqama = Escaped.IqamaNumber
    
    LEFT JOIN FEVisaNew FEVisa ON AX.Iqama = FEVisa.IqamaNumber
    
    LEFT JOIN TermOKSANew TermOKSA ON AX.Iqama = TermOKSA.IqamaNumber
    
    GROUP BY AX.EmployeeNumber, AX.EmployeeName, Active.ResidentName, AX.BasicSalary, AX.NetSalary, AX.Region, AX.LaborGroup, AX.Gender, AX.StartDate, AX.ContractEndDate, AX.Passport, AX.Iqama, AX.StatusEng, AX.SubStatusEng, AX.LocationDescription, AX.ProfessionEnglish, AX.Nationality, AX.ClientName, AX.CustomerContractNumber, AX.Religion, Active.PassportExpiryDateHijri, Active.IqamaExpiryDateHijri, Active.ActiveStatus, ERVisa.ERVisaIssuanceHijri, ERVisa.ERVisaExpiryHijri, ERVisa.VisaType, ERVisa.InOutKingdom, Escaped.Status, FEVisa.FEVisaIssuanceDate, FEVisa.FEVisaTravelBeforeDate, FEVisa.FEVisaStatus, DL.DrivinglicenseType, DL.DLIssuanceDateHijri, DL.DLExpiryDateHijri, TermOKSA.Status

  13. #13
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19
    I posted sql...can you explain more..

  14. #14
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What I am trying to get to is the SQL string from SQL Server that can be used in the VBA code above, and all you have to do is insert the search text value from the form. I can't see where to put that in what you have posted here. I am looking for the part that says "WHERE EmployeeNumber=12345".

    Is this a pass-thru query? It doesn't look like it comes from SQL Server.

  15. #15
    Sweetu is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by aytee111 View Post
    What I am trying to get to is the SQL string from SQL Server that can be used in the VBA code above, and all you have to do is insert the search text value from the form. I can't see where to put that in what you have posted here. I am looking for the part that says "WHERE EmployeeNumber=12345".

    Is this a pass-thru query? It doesn't look like it comes from SQL Server.
    Yes it's pass through query from sql server...
    Before I'm using this below filter...

    Code:
    'Me.Filter = "[EmployeeNumber] Like '*" & [Forms]![Navigation Form]![NaigationSubform].[Form]![txtSearch] & "*'"
    Me.Filter= "[EmployeeNumber] Like '*" & Me.[txtSearch] & "*'"
    Me.Filteron = True

    I tried your code in on click event but it doesn't seem to work..Either I'm using wrong or something needed to be change in code.
    Thanks

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Remote Desktop Connection Broker connection string
    By Philosophaie in forum Access
    Replies: 1
    Last Post: 09-14-2015, 03:51 PM
  2. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  3. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  4. DAO Recordset Connection for Access 2002
    By creativefusion in forum Programming
    Replies: 5
    Last Post: 08-17-2010, 06:45 PM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums