ado

All, Firstly, thank you for any help you can provide. I have a workbook test.xlsm, which has two sheets, Data and Report. Then, I have some VBA that creates an ADO Connection to Data in order to run SQL to pull some data from Data into Report, see below. When I open the workbook it runs just...

When using ADO to connect to/create an Access db, is it possible to execute SQL to create Tables, Primary Keys, etc...? or must this all be done using the ADOX.Catalog? :)

When using ADO to extract data to excel, is it possible to use 'CopyFromRecordset' to fill a Combo/Listbox? Or is it easiest to extract the data to a worksheet and fill the box from there?

I happened upon the following code for using ADO/SQL to extract a record count for Access Queries... Private Function Record_Count(sSQL As String) Set cnn = New ADODB.Connection With cnn .CommandTimeout = 0 .Provider = "Microsoft.Jet.OLEDB.4.0"...

I'm using Adodata to pull from a sql server, do a few things to it, then update another table. The issue is when a persons name has an apostrophe in it. Here's what I'm currently doing: ************************************** adocmd.commandtext = sqlstring set adodta = adocmd.execute ...

Hi Another quirk I can't see reference to. I am pulling a small recordset out of an Access database. It is a single field recordset. Some records are null. If I try and load the recordset into an array I get a mismatch error. The field is set as a text fields. On closer inspection I...

Hi All A little while back Andrew showed me that using the ADO Stream object I could preserve the recordset filter. I am using this: Public Function FilterRecordSet(ByVal objRecordSet As ADODB.RecordSet, ByVal strFilterText As String) As ADODB.RecordSet Dim objStream As ADODB.Stream...

Hi I have a recordset from a select * query to an access DB table. I want to be able to filter the recordset (using filter property). The users enter the filter expression in a textbox. After that they have a choice to take the data into a pivot table, or a csv file. The code runs without a...

I have a lot of trouble using Excel 2007 together with SQL server 2008 R2. I need to share data between two or more workbooks, and I want to do this through a SQL database (placed in SQL Server 2008 R2) on a remote server. I use VBA with ADO and SQL Server Native Client OLE DB provider as data...

Hello all Running this... Public Sub DBQueryRunExecution() Dim DBFullName As String Dim Cnct As String, Src As String Dim Connection As ADODB.Connection Dim Recordset As ADODB.Recordset Dim Col As Integer Dim LR As Long Dim DB2U As Long Dim FilterStr...

Hello, diving into cross access/excel stuff here. Well, not really, but I'm using disconnected ADO Record Sets to index my massive worksheet tables (30,000+ rows). The whole thing runs beautifully. However I now want to be able to merge/pull/export/import different 'Data' sheets around, so for...

Hello, so kind of two questions: I have a 'data' sheet (table) hidden, and to optimise speed at run-time I load the thing in an array... Global RecDict As Object, Database() As Variant, Sub Startup() Dim RecStore As Range Database() = Sheets("Data").Range("A2:Z" &...

Hi All, I have an excel spreadsheet that is linked into an access database through ADO connections. The spreadsheet is set up to make updates and adds directly into a table in my database. The database is on a server, what is happening is, and I don't know the cause, the database is being put...

Hi, I have an ADO recordset whereby the data returned is all text. When I pass the recordset to my pivotCache object, the pivot table will be created but since the data is all text...the pivotfield type is set to "Count of.. ". Question : is there a way to convert the recordset data to the...

Hi, I have succesffuly imported data from my ADO connection to my worksheet. However, i would like to know 1 - how do I create a pivot table with my recordset object Thanks in advance :)

I'm using ADO to access a table in Excel and return data based on criteria, any data greater than 255 chars is simply cut at this point. Is there something I can include or use to get it to return the data (at least to the 1024 char limit). My code is below Thanks in advance. GTW Set cnnDD...

Hello I would ask for help with a macro, which has a table that is working but you need to export it to SQL, using a button on the Excel sheet. Use an example(Http://www.excelguru.ca/node/18), but when connecting to Microsoft sql 2008 is not working. It seems I have connection problem with...

Hi Guys, so I have a proc that when run in SQL Server returns a 4 teir window for the results (4 result sets) and each one contains juicy info that I want except the first one, Running the proc in VBA using ADO unfortunately only seems to return the first result set and dismisses the rest, is...

Hi Does anyone know how to create a new recordset as an exact copy of an existing pivot cache recordset? I can't access the external data that the pivot table is created from. I would like to either create a new recordset that is an exact copy, or output the entire existing recordset to a text...

Hi All I'm toying with sorting arrays using ADO recordsets... I know there are other ways of processing arrays but for now I'm just trying to understand how to process recordsets... No problem sorting numbers, but I have trouble sorting text. The code below works a treat but if I substitute...