DB/Reporting :: Making UnCrossTab Query In Access With VBA?
May 27, 2010
converting a speadsheet into a proper data source. I have imported it in to a table called "B" in access 2007. The problem is that the data is spead out of multible columns and rows, looks like a crosstab query. I need to convert it back to its data. I could do it with UNION Queries but this would be unpractical due to the number of columns.I have created a public function in my module. But I am not sure on how to call it. I get the error:
Quote:
Circular reference caused by 'BOut'.
on this line:
Code: Set rstout = CurrentDb.OpenRecordset("BOut", dbOpenDynaset)
Here is my first try:I have a table called "B":
Quote:
InvoiceCode,XXX001,XXX002,XXX003,...
A,$10,$15,$20,...
B,$1,$2,3$,...
[code]....
View 1 Replies
ADVERTISEMENT
Jan 20, 2010
WIth vb 2010 beta2, is it possible to use a Microsoft access save file instead of sql server (with no reduced functionality)?
View 2 Replies
Aug 13, 2008
I have the following query that works fine if I cut/pase into an SQL query in access, but when executed in VB the loop is skipped - ie, no data is returned.
[Code]...
View 5 Replies
Jan 28, 2011
DB/Reporting :: Access Insert Query does not inserting
View 2 Replies
Jul 15, 2008
I have an Access XP database with two linked tables and a maketable query. I need to first run the Query through VB 2005 and then get the data from the resulting table created. But I get an error when I run the query as follows. I am getting the SQL Select statement from the SQL view in MS access after I create my Maketable query So it should be ok. I am saving the select in a hidden text box on my form.The query text :
SELECT [SWX-Seats].[Asset #], [SWX-Seats].Account, [SWX-Seats].Product, [SWX-Seats].[Seat Id], [SWX-Seats].[Serial #], [SWX-Seats].Qty, [SWX-Seats].[Entitlement End Date], [SWX-Seats]![Entitlement End Date]-[NS-Seats]![Exp Date] AS DaysDiff, [NS-Seats].[Exp Date] INTO
[code].....
View 2 Replies
Apr 15, 2008
What I am trying to do basically is open an access query in a datagrid view and then be able to make changes to the datagrid and then save it back to the database.When I try to save:
Me.BindingSource.EndEdit()
Me.TableAdapter.Update(DataSet)
It says that update is not a member of the tableadapter... Why is that?
View 1 Replies
Apr 9, 2008
A portion of my code is below. I am making a database application using FlexGrid and an ADO Control. I get errors on the statements following .saying that "Overload resolution failed because no accessible 'Fields' accepts this number of Arguments". Can someone explain this error and also a fix for it? I am using Visual Basic 2008.
[Code]...
View 1 Replies
Apr 12, 2012
In a program I am creating, i want to let the user add and remove fields on an entity, lets say "people". So I have a table named "People", with ID and Name columns. Now, I want to let the user choose "add field" in a webpage, define for instance "email" field, and then use "ALTER TABLE" from the codebehind to create the new field in the table. What I want to know is: is this recommended? It seems like a simple way to do this, but since I have never seen any other programs doing it like this (Using ALTER TABLE statements from codebehind in a live environment) - and since it changes the design of the DB -
View 3 Replies
Apr 28, 2010
I am connecting to an Access database and using a parameter query with the LIKE operator to return all rows that match query. The string to search for is taken from a Textbox
sql =
"Select * FROM Allview WHERE Info Like" &
"*" &
CStr(TextBox1.Text) &
"*"
The query does not return any data in vb, but when run from access with same string, there is data returned.The connection to the database is done correctly, as I am able to return data with various other queries.
Partial code :
Dim
con As
New OleDb.OleDbConnection[code]....
View 8 Replies
Apr 7, 2012
I am trying to run a query an Access db from VB. The general query which I want to run is
SELECT * FROM Patient WHERE Patient.PatientID = ?
or SELECT * FROM Patient WHERE Patient.PatientLname = ?
I tried using an input box which captured the user input and pass that variable to the query, but that failed. Then I read about writing a function and using that, however, I keep getting an error which says Function not defined, but when stepping through the code, it seems to work. Here is my function: [Code]
View 1 Replies
Feb 25, 2011
I have a form that when you click a button it begins a query. I want the user to know that they already started a query and they basically need to wait and not hit the button again. To do this I have a list box that has an lbResults.item.add() to it
Code:
Private Sub btnControlKSMS1117Query_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnControlKSMS1117Query.Click
[code]....
View 1 Replies
May 22, 2008
anyone knows how to fetch the query of a view (Sql server) in vb.net?
View 3 Replies
Aug 2, 2010
I'm fairly new to visual basic and have a problem that is probably quite easy to solve yet I have been unable to do it so far and am about at wits end.I have an access database with a table that contains a list of cities and states. what i would like to do is to populate a combo box on a vb form with a list of the states in the states column. the query that i have is as follows:
SELECT DISTINCT State from Cities I have previewed the data in the dataset designer and it works, i just can't figure out how to make it fill the combo box. i initially created a table with just all of the states and then used a binding source and table adapter to bind it to the combo box, but if I'm able to figure this out this would help with my application going forward
View 1 Replies
Jul 17, 2008
I have a string qText with the text of a query in it, say qText = "select * from Data". how can I execute it to fill a TableAdapter I have in my form with the resulting data?
View 2 Replies
Jan 14, 2009
I am writing a little program that does the following:Allows the user to enter a id number.Then when the find button is clicked, we connect to the database and run a select statement for the id.I am trying to figure out how to get the first name, last name and birthdate into textboxes on my form.
I would like to have a way to display a messagebox if the resultset = 0 so as to tell the user no records found.Then if the information is correct I am going to have a button to update the information which is going to call a stored procedure I have in place to update the record.how to make the connection which I think I have working and then loading the individual fields into the appropriate textboxes.
View 1 Replies
Jul 30, 2009
I want to ask how to i , passed the value i get from my previous form using this"Login.user.Text" to a Query Builder to filter the result based on the value of Login.user.Text" .
SELECT LecturerID, Lecturer_P
FROM Lecturer
WHERE (LecturerID = ??? )
[code].....
View 3 Replies
Apr 18, 2008
i'm trying to make a query to a sql database, so when the user enters the id, the columns related to that id will be displayed. but, im getting an error: "Object reference not set to an instance of an object"
the code:
Code:
Try
TextBox1.Text = InputBox("Enter the Id.", "fsdf.")
query = "select * from Students where Id = '" & TextBox1.Text & "'"
[Code]......
View 4 Replies
Oct 9, 2009
So I'm working with a mysql database, and I already have some queries down. I have to make a windows application form in vb.net that can display the results of these queries in a dataGridView.Lets say I'm working with frm1, and i have datagrid1 which will be filled using the query "select * from xyz" when btn1 is clicked.Can someone please take me through the right steps to do that? References needed, connections to be made, etc. I did look through online materials, but they are mostly way too long and not dataGridView specific
View 2 Replies
Nov 15, 2008
I have a simple "two-tables" database (SQL EXpress) and a very simple application (VB 2008 Express). The two tables are customer and order and their structures are:
customer(IDcustomer, LastName, FirstName) IDcustomer=primary key orders(IDord, Number, Description, IDcust)
IDord=primary key the tables are related (IDcustomer-->ID cust)
In VB2008 environment I create a dataset, by means of Data Source Configuration Wizard, which encompass both customer and order tables (xsd file). By opening xsd file I set the relationship in the following manner:
"Both relation and foreign key constraint"
Update Rule = Cascade
Delete Rule = Cascade
In my form I show customer table in detail-mode and orders table in datagridview-mode. Two Fill method are created in the Load event and they work correctly.In "CustomerTableAdapter" I create, with QueryBuilder, a search query (say LastName query) and I write the following code:
Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.CustomerTableAdapter.FillByLastName(Me.CustomerOrdersDataSet.custom er, "D") 'All Lastnames with first letter = D
[Code]...
View 1 Replies
Oct 28, 2008
I'm trying to run a query and display the result in a listbox. I can do it for a datagridview but not for a listbox .
Code:
Private Sub frmNieuweActie_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
strSql = "SELECT Acties.Actieid, Acties.Actie " & _
"FROM(Acties) " & _
[code]....
I get the correct amount of items in my listbox but it displays "System.Data.DataRowView" as item .
View 3 Replies
May 6, 2012
I'm using vb.net express with access 2010.Everything is working fine except for a simple query:SQL: SELECT * FROM Customers WHERE Name Like 'Ale*'Which returns all the names starting with Ale. Now in my .NET code I wrote this:cmd = New OleDbCommand("SELECT * FROM Customers WHERE Name Like " & Chr(39) & Name.Text & Chr(39), connection)Now when I start the app and in my textbox "name" I write Ale* I have the following expression:SQL: SELECT * FROM Customers WHERE Name Like 'Ale*'But when it executes from .NET (dr = cmd.ExecuteReader) it doesn't find ANYTHING.
View 11 Replies
Apr 2, 2008
I am trying to query a SQL db and send the results to a notepad or wordpad doc. I have the VB code wrapping the statement and it works fine but I have to put the results to a doc so the user can preview and/or print.
View 3 Replies
Mar 2, 2011
it seems that this query doesnt read the begindate and only displays record with enddate.
Code:
str = ""
str = str & "Select sum(quantity) as totalpurchase from purchaseorder where itemcode='" & itmcode & "' and"
str = str & " category='Consumables' and company='" & company & "' and (datereceive >= '" & datebegin & "' and datereceive <= '" & dateend & "')"
str = str & " and (postatus='received' or postatus='oh-hand')"
i am using vb2010 and mysql community server.
View 3 Replies
May 22, 2012
I do not do a lot with complex queries but recently I had a need to do some funky data mining for a customer. I've removed the complex bits for ease of understanding. Basically I want everything that matches a date range and a group of status codes that can occur when a product leaves a department. I need to further filter the table for records that match the department code.
[Code]...
View 3 Replies
Apr 8, 2009
I would like to post the result of a query in a textbox. [Code] Suppose this is my table, then I would like to post the sum from aantal from in a textbox. For UserID 1 it would be 35, for UserID it would be 35, [Code] but how do I get the result in my textbox?
View 2 Replies
Dec 6, 2008
I have a stored procedure that I need to be able to change the table name that the select statement in the stored procedure uses depending on a user input, however I am having a problem with the code:
Code:
CREATE PROCEDURE [dbo].[spQryParentComments] @projTable as varchar(50)
AS
[code].....
View 3 Replies
Apr 17, 2009
populate a simple combobox with a query to a MySql database. I Just want the combobox to show the results of the query:
SELET DISTINCT models from Cars
I am using VB 2008 with Datasets, how can I achieve this?
View 2 Replies
Apr 16, 2008
What I want to do is be able to active a query for my datagrid by clicking on a button. How would I do that? Would I create the query as part of my tableadapter???
View 4 Replies
Aug 4, 2009
There is a connection in my program to SQL Server and there is a query which is bound to a combo box, to pull out values from the database. For some reason, I get an error message during run-time, saying that the syntax is incorrect.
Here's the query:
'The SQL Command to be executed:
projs = New SqlCommand("use " & Me.ComboBox1.Text & "''" & _
"go" & _
"select name from projects where condition = 'Y'", cnn1)
cnn1 is the name of my SQL Client connection.
View 3 Replies
Apr 23, 2008
How to Filter a Database Query Using a List..
View 2 Replies
