this example runs a named query in Access but it does not return the header row, just the data.
Code:
Sub RunAccessStoredQuery()
Dim objMyConn As New ADODB.Connection
Dim objMyRecordSet As New ADODB.Recordset
Dim strSQL As String
[Code].....
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
I am trying to find out if it possible to directly "access" (no play on words here) data stored on MS ACCESS by using Excel's formulas.
I have a set of tables stored on a Microsoft ACCESS 2010 database, and I want to use Excel to analyze that data, using formulas, such as SUMIF.
Is it possible, once I have established an Jet OLEDB connection from Excel to Access, to then directly exploit the data stored on Access without having to copy the data to Excel, and then use it.
I would like Excel to solely act as a tool to analyze, not to store data.
How to import data from an access database, specific tables, to excel using vba; the trick being the database is stored on SharePoint Server 2010.
View 2 Replies View RelatedI am using the following code to run a Stored Procedure Query. It works perfect bringing back 4 columns of data. But is it possible to make it so that it only brings back 2 columns of data. I need the 2nd and 4th, which are named "DateOf" and "Gen", respectively. Someway to include a select command?
I posted here, without luck: http://www.mrexcel.com/forum/showthread.php?t=367809
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
I'm trying to build a query which matches two tables which say has a number like A#### , I want the query to give me the A number's which do not belong in the second table but which do in the first table. i believe i need an SQL query to do this?
View 2 Replies View RelatedI have created a worksheet with headers on each column each has different and used the =MIN function to find the lowest value. All good so far.... Now instead of returning the value I would like it to return the header text.
For Example.
Four columns Tom Paul Harry Bob
Tom has a value of 10 in the cell below.
Paul has a value of 5 in the cell below. and so on with Harry and Bob
Using the =MIN(A3:D3) I get the lowest value (in this case)5. I want to the returned value as Paul instead of 5.
i just want to know what function will i use to return a header row as
a result. I have a worksheet consisting of lists of images arranged in
columns by subfolder; the name of the subfolder serves as my header.
Now, i have another list of images and i want to crosscheck this list
to my worksheet to know where these images are located or in what
subfolder/s are they included.
my table looks like this:
1 Sub1 Sub2 ... Sub20 (header row)
2 img1 img101 img201
. . .
. . .
img100 img200 img300
what i want is: if i search for img150...the result would be
Sub2(header row)
i am trying to use VBA to query a access database that i have i want to be able to pull the information that matchs either textbox4 or 5 i am using the code below but i am getting global errors.
Private Sub CommandButton1_Click()
Dim custname As Variant
Dim Custnum As Variant
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
custname = Range("TextBox5").Text
Custnum = Range("TextBox4").Text
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=Insolvency;Description=Insolvency;APP=Microsoft Office XP;DATABASE=Insolvency;Trusted_Connection=YES"), Destination:=Range("Listbox1"))
.CommandText = Array("SELECT * FROM POST WHERE Customer_Account_Name=" & custname & " AND Customer_Account_Number = '" & Custnum & "' ORDER BY Customer_Account_Number")
.Name = "Insolvency Post Query"..........................
I'm at a stand-still again, as my current task calls for a way to communicate between Access and Excel. I know where the data I want in my Excel workbook is, and there's a query in place to pull it into a table. What I'm trying to do is write a macro that will open that particular table in Access, run the query, select the table, open a particular worksheet in my workbook, and then paste the data in.
This isn't hard to do by hand, besides finding the right database among a mess of others. But I want someone who isn't me to be able to get ahold of the same data by running the macro. It'll make less work for me in the long run.
I have the below code that will do a data dump of an access table into excel based on an SQL query, is there a way to do a select statement that can match against the data in column C on the spreadsheet.
like:
"Select * From Bsmart where Serial_Number = ##[EXCEL COLUMN C1:Lastrow]##
Code:
Dim bkNew As Workbook
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String
Dim SQl1, SQL2, SQL3, SQL4, SQL5, varSQl As String
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
[Code] .....
I have an access database that has many queries and in order to speed it up I have a Macro in access that runs it in no time, but I need to be able to run the query in access via a trigger in Excel and have come up with the below, but it's not working.
View 9 Replies View RelatedI have been trying to pass a query to fetch data from Access database.
The query is:
sSQL = "Select Client+, Entry-ID, Type of Contact, Manual Creation, Date Created, Transfer-date, Respond SLA FROM BASE DATA"
The problem I am facing is in:
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText
The error I am getting is:
Run-Time error '-2147........'
Method 'Open' of Object '_Recordset' failed...
query_from_MS_Database_Database. how do i reference by name a query from an Access dbs (e.g., qyery_from_MS_Database_Database)...?
View 4 Replies View RelatedI have the following code for importing a query from Access to Excel.
Sub LoadAdditionalData()
Application.Run Range("AUTOSAVE.XLA!mcs02 .OnTime")
With ActiveSheet.QueryTables.Add(Connection:= Array(Array( _
"ODBC;DBQ=S:NBHD_RENEWALDatabaseNR DB_current.mdb;DefaultDir=S:NBHD_RENEWALDatabase;Driver={Driver do Microsoft Access (*.mdb)};" _
), Array( _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _ ...............
I currently have filters set to 990 (finance code) and 3 (quarter) but I would like to be able to enter these parameters myself everytime the macro is run.
i have code accessing an access database running a "query" which has been defined in the database itself. i asm using ADO - no issues with this. works very well.
my problem arises when i change the query to take a parameter. this works ok when i execute in access but not with the code i am using for VBA / ADO. i have tried several techniques but none seem to work.
Dim rsReport As New ADODB.Recordset
Dim cnReport As New ADODB.Connection
Dim cmdReport As New ADODB.Command
Dim prmReport As New ADODB.Parameter
cmdReport.CommandType = adCmdStoredProc
cmdReport.CommandText = "testQuery-withParam"
With prmReport
. Name = "FullName"
.Value = "joe bloggs"
.Type = adChar
.Size = 8
.Direction = adParamOutput
End With................................
I need to return the column header in a table which corresponds to the column that the value I'm querying is in. I've been trying to use the INDEX function coupled with MATCH and COLUMNS function but I'm not getting anywhere. I've found on the web a solution that uses SUMPRODUCT but I can't get that to work either. Unfortunately, I can't supply the data I'm working with as it is confidential. I know my question would be easier to answer if I could supply it.
View 2 Replies View RelatedI'm trying to query Access for some data. One of the fields I need to grab is called MOD, which also happens to be a VBA function. How do I get Excel to recognize MOD as a field name instead of a VBA function? (Renaming it is not an option)
Code:
stSQL1 = "SELECT MOD FROM CST_ClaimbyLop WHERE CoCName='" & myCST & "'"
I have to import data into Excel from Access (yes, I have to import it to Excel and cannot use Access exclusively). When I import; however, the formatting does not come over correctly, most especially losing the formatting of numbers. I need one of two solutions:
1.) How to get the formatting to move over correctly?
2.) Or how I can save the formatting of a table, then be able to apply it as a "saved" format? The biggest thing is that the numbers will changed the number of decimal places and/or change from general numbers when I want to see them as currency.
The top table shows how i want this formatted, the bottom shows how it is importing.
Using Excel VBA, I created a query (strQryName) in Access. With below code in Excel, I am trying to copy the data from this query to Excel spreadsheet. However, I am getting nothing but field names. The code returns empty rst. When I check the query in Access, there is definitely data in it.
Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection
Dim fName, queArchive, queWhere, Query1, strQryName As String
Dim e, f As Integer
fName = Sheets("StartHere").Cells(3, 3)
Set conn = New ADODB.Connection
queArchive = Sheets("StartHere").Cells(10, 3)
queWhere = Sheets("StartHere").WhereCombo1.Value
Query1 = Sheets("StartHere").Cells(12, 3)
strQryName = queWhere & Query1 & queArchive
I looking for a solution to change a Access Query Criteria using a VB code in Excel, without opening the Access DB and changing the criteria manualy?
My normal Job is it:
1) I Work all the time in Excel
2) At the end of the work in Excel, I need to open Access
3) Open a query (Called "MyQuery")
4) Change one parameter the Date: Between 01/10/2008 and 30/10/2008 (This for each month)
5) Run the query
I looking to get rid of points 2) to 5) by replacing with a Command button on an Excel sheet
Sub Change_Criteria_And_Run_Query()
Dim mydbase As Object
Set mydbase = CreateObject("Access.Application")
mydbase.OpenCurrentDatabase ("C:My doucmentsDB1.mdb")
mybase."MyQuery"."Date Criteria" = Between (Worksheet("Sheet1").Range("A1").value) and (Worksheet("Sheet1").Range("A2").value)
>>>>>The above line of code I have problems to get right!
mydbase.DoCmd.RunMacro "MyQuery"
End Sub
I am not sure how to setup the initial connection to the datbase from excel and was wondering what would be the best way to go about things. Is it possible to run the query stored in the database and pass it a value or would I be better off just coding it into a sqlstring within the macro.
Here is the query that is in the database...when run it asks for the "Status?" variable.
SELECT ordernumber, mobilenumber
FROM bookings
WHERE status = [status?];
So yes, really simple query but yeah just want to know how to pass it that variable from excel or can I just set it up in the actual macro to maybe read the status from a cell?
The code below (ran from Excel vba) deletes all data from an Access table, paste data from Excel into another table, and then run two Access Macro's and paste data from yet another table back into Excel.
My problem is that when I run the query from Excel, the data returned to Excel is always zero. When I step through the code, and check the Access database, all the steps are carried out properly. E.g, the delete query is deleting all the records in the correct table, the append query is copying the correct data into the correct table in Access. The two macros do run, but then when the resulting table is returned to Excel, it brings back zero's.
Doing all this manually in Access is returning the data correctly.
The two Access macros are running some queries, E.g. the "001 UpdateLatestData" is running two queries and the "002 CreateMonthlyResults" is running about 15 separte queries.
Sub OPENACCESSTABLE_DELETE_ROWS1()
Dim db As Object
Dim accApp As Object
Access_DB = ActiveWorkbook.Worksheets("MACRO").Range("C1").Value
DBPath = "C:MinetteProperty.mdb"
Set oApp = CreateObject("Access.Application")
oApp.Visible = True
oApp.OpenCurrentDatabase DBPath
'Delete Query
DoCmd.RunSQL "DELETE [001 non motor data].* FROM [001 non motor data]"
Set accApp = GetObject(Access_DB) .........................
Is it possible to use cell references to define parameters in an SQL query to an MS Access database?
I assumed it was similar to using SQL but I'm getting an error in excel.
Here is my query: .....
The error I get in excel is: "Invalid character value for cast specification" and it wont let me specify a cell.
I need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
View 8 Replies View RelatedI have created a query in an Access database which returns multiple results. I need to be able to run this query within Excel, but only showing the total of the results in a single cell.
I also need to run it multiple times on the same sheet using different dates. The Access query asks for a date each time it's run
I have only found ways to retrieve the entire results of the query into Excel based on the settings within the query.
I have an excel file that imports data from an access database. Now the user wants the database password protected I edited my code to include the line "user ID=admin;password=risk;" but I'm getting an error message stating: "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."
I'm bothered because I did not create a workgroup information file. I just want a password on the database I did not create users and groups.
Note this code works fine without the password protection and the added line.
My
Sub Update_Click()
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C: est.mdb;" & _
"user ID=admin;password=risk;"
szSQL = "SELECT * from data"
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
I have a database that has outgrown excel (over 77,000 rows now) so i now have it in Access. I want to replicate the following situation I used to have using lookups in excel with a query to the Access database: Paste a list of identifiers into an excel sheet. Values corresponding to the list are returned from the database in adjoining columns. The list can be from 10 to 300 cells long an returns data in 14 columns. I have had a go with the Import external data wizard which generates a query but have not been able to work out how to base the query on a list of cells in excel and have the query return values for each of the identifiers in the list.
View 2 Replies View RelatedI have a query which is giving me some trouble when returning the data from ms query. The query was written in mysql query browser and returns 2 columns of data - a date and a number. MS Query correctly processes the query but when the data is returned to excel only the second column is returned. Here is the query:
SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;
