Using TOP in a simple SQL string (VB6)
"SELECT TOP 25 * FROM " & "data.CSV" & "ORDER BY " & "ELAPSED" & DESC " Is the one that looks closest to working except it's missing some spaces. What language is this in? I don't know what the ampersands and quotes are all meaning, but if you can...
Populating combobox on userform from dynamic sql results
sql-server,excel-vba,combobox,adodb,recordset
There isn't any need to loop: If Not rst.EOF then Me.ComboBox2.Column = rst.getrows() should be enough to put the whole recordset into the control. GetRows returns an array transposed from the way you might expect, which is why I used .Column and not .List...
run-time error - 2147217900 80040e14
It is recommended to run large queries via recordset and not like sheet query table. To understand the IN operator see this. This is example how you can build and run your query: Sub BuildAndRun_Sane_Query() ' Create IN_string: Dim SN, S_In As String, s_query As String For Each SN In...
Is it possible to know how many Oracle connections are opened in Excel VBA?
excel,vba,excel-vba,database-connection,adodb
The answer is no... and yes. There is nothing built in to keep track of how many ADODB connections you have open to a particular database. You would have to keep track of this yourself if it is a requirement. You could do this by adding each of your connections...
What's wrong with this UPDATE statement that I'm trying to use in ADO Excel?
Put square brackets around position (seems to be a reserved word). Remove the IMEX=1 from the connection string, or you get an error "operation must use an updateable query" Revised code: Sub Tester() update "update [Regional Personnel$] set name='Ada Lovelace'," & _ " [Phone]='(303) 555-1337', [lan id]='ADL3', [position]='Engineer' where...
ADODB connection state property
State Property (ADO) indicates for all applicable objects whether the state of the object is open or closed. If the object is executing an asynchronous method, indicates whether the current state of the object is connecting, executing, or retrieving. Returns a Long value that can be an ObjectStateEnum value. The...
VBA User Defined Type not defined error
Make sure you have the reference to "Microsoft ActiveX Data Objects Library" set in your project (Under the Project > References menu): If you do not have this set, the runtime environment doesn't know about the ADODB library so it throws a not defined error....
creating Access db with ADOX
c#,visual-studio,ms-access-2010,adodb,adox
You are declaring the text fields as ADOX.DataTypeEnum.adVarChar but all Access Text fields are capable of storing Unicode so you need to declare them as ADOX.DataTypeEnum.adVarWChar.
Is there a more efficient method of using the SQL count mutliple times?
You could combine it all into a single query: SELECT (SELECT COUNT(*) FROM tbl_imts WHERE [REQUEST TYPE]='MINISTERIAL DOCKET' AND [OPI]='ASFBA' AND [DATE RECEIVED] BETWEEN #"+date1+"# AND #"+date1_2+"#") AS FirstColumn, (SELECT COUNT(*) FROM tbl_imts WHERE [REQUEST TYPE]='MINISTERIAL DOCKET' AND [OPI]='ASFBD' AND [DATE RECEIVED] BETWEEN #"+date1+"# AND #"+date1_2+"#") AS SecondColumn, Add other...
ADODB Connection is empty, Unable to read CSV file
sql,vba,connection-string,adodb,recordset
The connection string is blank because a wrong parameter is being sent to GetData. Take a look at these code snips: SourceSheet is the second parameter in the call, but you pass it as the first parameter here: ' Here's the call to GetData GetData("export.csv" ,"A1:BE", ... Filename goes in...
ADODB What is the meaning of parameters in CreateParameter
...CreateParameter(NAME,TYPE,DIRECTION,SIZE,VALUE) NAME: name of the parameter TYPE: must be a data type. 200, for example, is a varchar. DIRECTION: Input, Output, Retrurn Value,... 1 is Input. SIZE: Size of type. In your first example, 50 characters VALUE: The data. You can use more friendly values if include adovbs.inc If so,...
Assigning object reference to a Variant/Long
The Long data type can hold a 32-bit integer. It isn't an object reference, so don't use Set. Dim userId As Long userId = RS.Fields(0).value For more on what is and isn't an object, see Data Type Summary for VBA....
Permission denied in copying DB after connection closed
vba,ms-access,database-connection,adodb
Change my con.open from con.Open _ "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _ "Dbq=" & Loc & _ "Exclusive=1;" & _ "Uid=admin;" & _ "Pwd=;" to con.Open _ "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _ "Dbq=" & Loc & ";" And it seems to work now. ...
Is it necessary to specify CommandTimeout for both ADODB.Connection and ADODB.Command objects?
sql,vbscript,asp-classic,adodb
Short answer to your question, yes, it is necessary if you want to specify the ADODBCommand.CommandTimeout to a value other than its default setting (30, I believe). From MSDN: The CommandTimeout setting on a Connection object has no effect on the CommandTimeout setting on a Command object on the same...
How do I turn a QueryTable Connection into an ADODB connection?
sql,sql-server,excel,vba,adodb
This one will fail because you are appending extra text after the "Trusted_connection" parameter. If you use a trusted connection, you don't need a username or password, and the syntax is different for SQLOLEDB than with {SQLServer} (it should be Integrated Security=SSPI;. SqlConn.Open "Provider=SQLOLEDB;Data Source=[SeverName];Initial Catalog=[DBName];Trusted_connection=yes;", "[User]", "[Pass]" You also...
execute stored procedure with parameters using PHP ADODB, MSSQL sqlsrv
php,sql-server,stored-procedures,adodb,sqlsrv
As per my investigation it seems that ADODB driver for MSSQL does not support stored procedure with parameters. Use sqlsrv. $serverName = "XX.XX.XX.XXX"; $connectionInfo = array( "Database"=>"XXXXX", "UID"=>"XXXXX", "PWD"=>"XXX","ConnectionPooling" => "1","MultipleActiveResultSets"=>'0'); $conn = sqlsrv_connect( $serverName, $connectionInfo ); if( $conn === false ) { die( print_r( sqlsrv_errors(), true)); } $stmt =...
SQL to Excel table in VBA using IF EXISTS complains “Invalid SQL statement”
sql,vba,excel-vba,ms-access,adodb
You're using the ACE OleDb provider to interact with the spreadsheet. That means you can use only SQL features that provider supports for your data source. IF EXISTS is not supported in this situation. However, there may be an even more basic problem. I don't believe it's possible to alter...
VBA. Cant get data from .mdb file. Operation is not allowed when object is closed
Problem was in SQLRequst string : SQLRequest = "SELECT * FROM tblStack WHERE StackID='XXXXX'" It needed ''....
Manually created recordset not behaving like disconnected recordset
.net,datatable,vb6,adodb,recordset
.Net defaults to disconnected recordsets, but VB6 doesn't. The recordset that you have opened in your code is a Dynamic recordset, which is incapable of operating without an open connection. The dynamic cursortype means that all changes to your recordset are posted immediately to the database. When you closed your...
ADODB.Recordset keeping rows after close.. Classic ASP
Looks like you're not clearing the value of "value". Just before you close your recordsets, use: value = "" ... to make sure it's empty. Better still, use different variables between different code blocks....
How to select dynamic sheet names with an Excel VBA macro that moves data with ADODB?
You need to concatenate the filename into the string using & to concatenate so for example: strQuery = "SELECT * FROM [" & filename & "$C2:C100]" ...
VBA Multiple SQL query to Excel
sql,sql-server,excel,vba,adodb
Sounds like this should be its own Subroutine. You can call the Sub whenever you need it by passing in the procedure to execute, and the worksheet to put the results on. Public Sub Macro1(byval storedProc as string, byval ws as worksheet) ' Create a connection object. Dim cnPubs As...
sql join on string = integer to work on any RDBMS
mysql,sql,postgresql,join,adodb
Since you can't CAST to INT in the same way for each database due to data types, you can cast your numeric field to CHAR: CAST(a.numeric_Field AS CHAR(5))` = b.stringfield That will work on Postgresql, MySQL, SQL Server, not sure about others....
can't select unique records based on multiple criteria
WHERE goes before GROUP BY <> is an equivalent to != so you should pass value to compare your [report$].column with. Probably the reason for No value given for one or more required parameters. is a misspelled field name. For example : WHERE [report$].column <> '' GROUP BY [report$].column,[report$].columnIndex...
ADODB error: 'No value given for one or more required parameters'
I figured it out... I did not change the query to reflect the fact that I actually have field names now. So, F2 and F3 become [Fund ID] and [Fund Name]. (F1 is still not named in my data.) I am not bright. Thanks for knocking the screw loose StackOverflow!!...
Force ADODB return EOF if length of returned data equals zero
sql-server,vbscript,asp-classic,adodb
I added a comment earlier but seen as though everyone has jumped on the answer wagon here goes; SELECT Code FROM AS_Table WHERE NumAtend = 1234 AND LEN(Code) > 0 By adding the LEN(Code) > 0 will cause only results with Code with a length greater then 0 to be...
Open Connection and Recordset objects to use SQL for sheet to sheet data movement
excel-vba,connection,adodb,recordset
Is the "strConnect" parameter intentionally commented out in objConnection.Open 'strConnect Uncomment that parameter and things will hopefully work edit: also strSQL = "SELECT * FROM [Sheet$3] & ;" is wrong. It should be strSQL = "SELECT * FROM [Sheet3$];" (the $ sign was in the wrong place and there was...
'Type Mismatch' Error on ADODB.Recordset
excel,excel-vba,adodb,recordset
I haven't had this exact problem, but I've found that the recordcount property on an ADODB recordset is hit or miss. Your best bet is to rewrite the loops like: recordset.movefirst While Not recordset.eof <your stuff with your record> recordset.movenext Loop Also, to test that there are records in your...
UDF function in Excel running ACE SQL query, JOIN two tables does not work
It looks like you are not specifying the fields/columns in the join. Both currAddress and curAddress2 look like tables. The SQL should be something like: strSQL = "SELECT * FROM [Table1] " & _ "LEFT JOIN [Table2] ON [Table1].[Field] = [Table2].[Field];" Are Indeks and Indeks2 your field names? If so,...
ADODB failed to select Extended ASCII characters
The problem here is that the Excel driver is converting the strings to ANSI symbols, for some reason. Some "clever" code is converting the Ă character (258) to A (65). If you have the JET drivers with the ISAM Excel driver installed, then the following connection string will use them:...
Migrating ADODB connection from ASP to ASPX
asp.net,vb.net,asp-classic,migration,adodb
You do not use ADODB in DotNet. Technically, you can, but that's not the way to do. You use ADO.Net, IDataReaders, DataSets (loose or strongly-typed, I prefer strongly-typed). ASP.NET is not ASP. Don't feel bad, I was trying the same thing you are (albeit, back in 2002). Until someone told...
ADODB wait for query to complete before refreshing a different connection
You need to execute asynchronously and wait for it to complete. Please note that it will slow you down if you wait for each command to execute until triggering the next. cn.Execute sqlString, , adAsyncExecute Do While cn.state = adStateOpen + adStateExecuting ' wait for command to finish ' perhaps...
Select Statement for Excel Datasource
When you set HDR=NO the column titles from the excel table will be ignored and it will be used internal names. See older answer: c#, oledb connection string issue
Updating Excel sheet cell (the cell is a dropdown list)
sql,excel,vbscript,asp-classic,adodb
So i finally found the problem...It had nohing to do do with my SQL or the cell having a list,the problem was that the cell was marked(in the excel sheet) as a DATE but the List was a String so whenever I tried to write my string on it gave...
Creating dynamic ADODB connections in Classic ASP
This might do the trick: Just use an array of connection strings. From this you create an array of connections. Then you can iterate over this array and send your commands to the separate databases. dim connectionStrings(1) dim connections(1) dim curConn connectionStrings(0) = "Provider=sqloledb;Server=.\EXPRESS2012;Database=master;uid=youruser;pwd=yourpwd" connectionStrings(1) = "Provider=sqloledb;Server=.\EXPRESS2012;Database=model;uid=youruser;pwd=yourpwd" for curConn =...
Does an ADODB Recordset generate SQL upon update?
I performed some tests and found that ADODB does in fact still run SQL Update statements. No voodoo as feared.
Field found by index and not by name
sql,ms-access,asp-classic,adodb
Try changing to: Response.Write(Recordset.Fields("Song")) ...
PHP - How to check result of successful query for updating in Adodb?
if i dont remember wrongly, use Affected_Rows( ) ...
In ASP, how do I return multiple recordsets from a parameterized query?
Here is a basic example, the magic line is; Set rs = rs.NextRecordSet Pseudo coded so apologies if there are some typos. Dim cmd, rs, sql, connstring Set cmd = Server.CreateObject("ADODB.Command") connstring = "your connection string here" sql = "" sql = sql & "SELECT col1, col2 FROM table1 WHERE...