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 RepliesI 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 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 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?
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 an Access table which has following fields:
ScanDate Number DataType
Type Number
Type1 Number
Type2 Number
BatchNo Number
Cases Number
Pages Number
Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.
See attached the Excelsheet where the data should be populated to. WBCount.xls
I've about 10 pivot tables in Excel that are populated via an External Query link to Access. In each case I have pre-written views with all the columns I need so that the query can just pick them up. Nine of the ten queries work perfectly, but one doesn't - reporting "Too Few Parameters - Expected 1" when I try and click on the "Return Data to Excel" in the import wizard.
I can see the column headers in Excel to select; so I'm assuming that it can parse the SQL but no results are being returned. Just to confirm though - results are returned (>8000 of them) in Access.
SQL code below:
Code:
SELECT z.pathdurationhours AS PathHours, Count(z.pathdurationhours) AS Paths,
Count(z.pathdurationhours)/(select count(*) from (select [activity date],
[conversion ID], count(*) from tbl_dfa_p2cdata group by [activity date], [conversion ID]) ) AS PCT
FROM (SELECT a.[activity date], a.[conversion ID], val( max(a.[interaction time])-min(a.[interaction time]) )
*24 AS PathDurationHours FROM tbl_dfa_P2CData AS a WHERE a.[interaction time]
"" GROUP BY a.[activity date], a.[conversion ID]) AS z
GROUP BY z.pathdurationhours;
I use Office 2010. I have an Access database that goes out and runs various queries from our systems of record. It then takes the data and dumps it into an Excel file. I have taken the info and created a file the grabs the raw data, converts it into Pivots and then Charts. All info is automatically updated however the size will change depending on what department is quering the data. It is really all done with a click of a button. I have tried to merely uncheck the "(blank)" but it comes back everytime the report is ran.
How can I keep it from returning??
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
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 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 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................................
The current code is not working.
View 1 Replies View RelatedI have a large excel file with lots of data that it takes a long time to manipulate in excel. What I would like to do, is to import it in access, do some manipulations, and export it in excel again.
All these have to be automatic, so that the user does not do anything. Is it possible to do so through some macro?
I'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.
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].....
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 have a CSV that I have a excel macro created to do some formatting and then export the data into Access. that part works. I also have it checking for duplicate entries and skipping them. The problem that i run into is that not all my duplicates are truly duplicate, there have been updates and now i need to update Access. I have 2 fields, my primary key which is what I'm primarily matching on and then I'm checking if its been updated by checking another field "Last Changed" If this field doesn't match then I need to update the record.
At this point I'm trying to use a SQL UPDATE and I now believe that i have it formatted correct but when it gets to a record that needs to be updated I get an error: Run-time error '-2147217887 (80040e21)': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. I'm not sure what I'm doing wrong. the only other thing i could think to do would be to check each field to see if it has changed and just run the query against that but I feel like i would run into the same issue.
This is how I'm connecting to Access:
Code:
strTableName = "TableName"
strDBLocation = "xxxxuserhomexxxxxxxxxxxxDatabaseDatabase Backend.accdb"
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBLocation & ";"
Set cn = New ADODB.Connection
cn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
And this is the Query I'm using:
Code:
strSQL = "UPDATE " & strTableName & " "
strSQL = strSQL & "SET [Work Order ID] = '" & Range("B" & r).Value & "', "
strSQL = strSQL & "[JOBSTATUS] = '" & Range("C" & r).Value & "', "
strSQL = strSQL & "[SUBSTATUS] = '" & Range("D" & r).Value & "', "
strSQL = strSQL & "[Job Status] = '" & Range("E" & r).Value & "', "
strSQL = strSQL & "[STATUSDATETIME] = #" & Range("F" & r).Value & "#, "
[Code] ..........
The SQL code sits in a loop with an IF so it shouldn't execute every time and I plan on once i get this working to revisit this code and try to optimize it but that is not a priority currently.
I'm using both Excel 2007 and Access 2007 on XP.
I am not able to access *.rpt files in excel macro
By using this below mentioned codes am not able to open the files from folder.
Code:
fpath = "C:Documents and Settings est"
f = Dir(fpath & "*.xls")
Workbooks.Open Filename:=fpath & f
I am attempting to run a MS Ecel macro that is stored on a MS Excel shared file in a MS Ecel target file (locally stored on my C drive) from a VBA module in Ms Access. That is, from a code i want to open the .xls file that houses the macro, and then open the .xls file that i want to run the file in, and then run the macro.
Here is my situation: the file that houses the macro has the workbook hidden and causes in error.
if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set.
if i hide the workbook the vba coding can't find the macro...run time error 1004
once again, i currently have an excel file that houses macro whose workbook is hidden.
does anyone know how this can be accomplished?
here is the ....
I manually copy a variable number of rows from an Excel worksheet and then paste/append them into an Access table.
I would like to have a macro for this to automate the process a bit, and I can always add the code to a button or menu item later.
Data setupRange starts at A11:X11
Number of rows to copy will vary depending on the data within the sheet
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 Related