ado connection

  1. vds1

    SQL query works in MS Access but doesnt when called from excel VBA

    Hi Gurus - Thank you for looking into my problem. Below query works when i execute in MS Access. Its a standard query which i plan to call in Excel using ADO. select * from qryForwardRecon; When the above query is called using ADO in Excel VBA , i get zero records. Not sure what could be...
  2. S

    Trying to get the Identity right after .Update using SQLOLEDB

    I'm just wondering if anyone knows why I have to execute the query twice in the code below to get the Identity? Example: Set rs = Cn.Execute("SELECT @@Identity as NewID", , adCmdText) x = rs.Fields("NewID").Value '''returns null Set rs = Cn.Execute("SELECT @@Identity as...
  3. M

    Read text file using ADO and populate a... Listbox in this example

    The below sub i created to populate a listbox using ADO to read a text file as Access and SQL were unavailable. I needed 2 columns from the txt file and one would differ per user so i used the persons computer username as the column name i would need (so it would work for anybody in the table)...
  4. bfreescott

    ADO queries are throwing errors that files are already open.

    I have an ADO query that pulls data from other excel files (without opening them - thus, the reason for choosing ADO to begin with), but the queries are erroring out when the file being queried is already opened by another user. Have you seen this or been able to resolve it?
  5. G

    ADO connection, Execute method, and Excel VBA...

    Dear friends, I'm creating an Excel Add-in that has several functions. Also I have a bid data in Access (more than 10 000 rows, and several tables). The Add-in connects to DB when it opens. Then every function in the Add-in use RecordSets to read data from Access DB and do some calculations...
  6. A

    Access front-end with SQL Server OLEDB

    I am creating an Access application that we need to use with SQL Server. I have a some questions that I have not been able to get answers for yet after doing quite a bit of research. I am planning on using a OLEDB connection, However I am a little confused about how to set this up. I have an...
  7. R

    Excel Vba handling errors from Ado Connections/permissions to SQL server.

    Hi All, My spreadsheet is a front-end for users to execute queries/store procs against SQL server databases and output the rowset(s) to worksheets, this is all working well. It's the error handling I want to improve, currently my error handling consists of the usual "On error" and displays the...
  8. K

    What's wrong with my VBA code?

    Hey guys, Trying to create a macro button that, when pressed, populates empty columns, based on a match between Part_ID in excel sheet called BOM and Part_ID in database table called materials joined with tables manufacturers and vendors The code is as follows: <code style="font-family...
  9. M

    Using ADO to Save, Recall and Update records in Access using Excel 2003

    I'm not too familiar with Access and linking to it from Excel so thought that on a recent project I would try and build up my skill set and use ADO, plus the use of a database in this instance was appropriate. So far, I have figured out how to Recall a record that matches a certain condition...
  10. D

    Import table from Access to Excel using VBA (2007)

    Hi - I am creating a custom ribbon in Excel to perform a variety of analysis on a dataset that is refreshed everyday. The first button I have included on the ribbon is one that I would like to import data into an Excel worksheet from an Access table. I have the following module, however, I am...
  11. F

    2 sets of ADO connections to two different sheets in same procedure

    Hi, I am working on a procedure and just want to know if 2 ado connections are possible at once like this because I am getting an error that says "Object doesn't support this property or method" and the highlighted code is causing it but I know the field exists. Thanks! Dim row As Integer Dim...
  12. B

    ADO SQL Connection Closing

    Sub ConnectDB() Dim sFileName As String sFileName = ActiveWorkbook.FullName Set cnn = New ADODB.Connection cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFileName _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" End Sub Sub ImportPri ....... Call ConnectDB...
  13. G

    Cancel ADO connection to mySQL (from Excel)

    Hi I will soon have a excel sheet distributed between hundreds of users. When the excel is first opened, it gets connected to a mySQL database (and stays connected until excel application is closed) People then do queries, etc using the following code. ' connects to database when workbook is...
  14. B

    Run-time error 3706 - Provider not found

    Hi Guys, I am building an application for Excel 2003 which pulls data from an Access 2007 database using an ADO connection. I have successfully tested this using Excel 2007 in 2003 compatibility mode using : strPathName = ThisWorkbook.Path MyConn = strPathName & "\Test.accdb" Set cnn = New...
  15. J

    Merging two tables using multiple columns

    Hi, I have two tables. I should merge the two tables and form an output table. <table style="border-collapse: collapse; width: 203pt;" width="271" border="0" cellpadding="0" cellspacing="0"><col style="width: 35pt;" width="47"> <col style="width: 69pt;" width="92"> <col style="width: 51pt;"...

Some videos you may like

This Week's Hot Topics

Top