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'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?
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"..........................
The size of the table I'm importing will change, so i would like for the code to not matter on size. Also its, gonna be large too. The sheet will always be the same and the column headers will match for excel and access.
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.
"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
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.
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 am trying to import database records into Excel, but i keep getting an error "run-time error 424" on the code below.
It is probably something simple, but i havent tried importing from Access to Excel before.
The code below should clear the data from "Existing" and then copy the data from test.mdb into the same sheet.
The error occurs when opening "Data Source"
Code: Private Sub Workbook_Open() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim dbCommand As New ADODB.Command RowCount = Worksheets("Existing").Range("A" & Rows.Count).End(xlUp).Row Worksheets("Existing").Range("A2:V" & RowCount).ClearContents
I am having some issues importing data from Excel to Access. I am attempting to import about 45000 rows of data from Excel to Access. It appears Access is only importing about 16000 of the 45000 rows. I am using 'import' in 'get external data'.
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.
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
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
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!
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 working code to copy the matching records of an Access table into an Excel worksheet, but now I am trying to modify this code so that I can select a couple of specific fields from the table instead of just copying all of the fields (there are 15 fields in the table, but I only actually need 4 of them for this particular report).
Here is the code I am currently using to copy all of the fields over with the matching records:
Const myDB = "DSD Errors DB tester.mdb" Private Sub CommandButton4_Click() ' Test Field Select button Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim sSQL As String
sSQL = "SELECT * FROM DSD_Invoice_Requests WHERE `Paid?` IS NULL"
How can I modify this so that I put specific fields from the table into columns in Excel that will not line up with the layout of the table? For the sake of the example, let's say that I need fields 2, 4, 6, and 8 of the table to go into columns 1, 2, 3, and 4 in Excel.
I tried to upload my spreadsheet but the zipped excel spreadsheet was 78KB over the forum max. Its hard for me to explan this without you looking at the spreadsheet. Basicly I'm exporting data from Access to Excel into a spreadsheet. When the data exports into excel I want the data to be formated in such a way and this is where some code will need to be written. I've made up 2 spreadsheets within excel. One sheet is how the data comes into excel from access and the other sheet is how I'd like the data to look without me having to doctor the sheet every time I export.
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.
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.
Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String