Passing Data From A Query Table To An Aggregate Table(MS Access 2000)
hello....I am currently developing an accounting system for a specific company here in the Philippines...Can anybody help me or give me a code...I have an aggregate table named AggSales to be used as a source data for my report(Crystal Report 8.5), which is a comparative Profit & Loss report of two months...Now my problem is, i want to pass data from a query table named qryGenLedger to the AggSales table by which the user will select a month and a year of their choice(Month1,Year1 and Month2, Year2)...so that the data in the AggSales will be composed only by Dates which matches the choice of the user(Month1,Year1 and Month2,Year2)...thanx...please help!!!
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Passing Data From A Query Table To An Aggregate Table(MS Access 2000)
hello....I am currently developing an accounting system for a specific company here in the Philippines...Can anybody help me or give me a code...I have an aggregate table named AggSales to be used as a source data for my report(Crystal Report 8.5), which is a comparative Profit & Loss report of two months...Now my problem is, i want to pass data from a query table named qryGenLedger to the AggSales table by which the user will select a month and a year of their choice(Month1,Year1 and Month2, Year2)...so that the data in the AggSales will be composed only by Dates which matches the choice of the user(Month1,Year1 and Month2,Year2)...thanx...please help!!!
Multiple Table Query With VB 6.0 And Access 2000
Hello all I was wondering if any one can help me with this. I am trying to display the records of multiple tables in a MSHflexgrid. I am using an ADODC to access my database. The task I am trying to perform is when the user chooses a purchasin ID and the Company ID then press ok( command Button) it is query the database and display the results in the flexgrid. The purchasing ID is in the Purchasing table and the Company ID is in the Company ID and also the Purchasing table. I am not sure how to do a query with multiple tables. I know you need to put the table name in front of the field name for example purchasing.purchasing_ID. What I need to know is the SQL query itself. I have this so far but it is giving me a runtime error saying that there is a problem with my FROM statement.
Dim field As String
Dim field1 As String
Dim MyQuery As String
Dim op As String
op = "and "
field=purchasingIDCombo.Text
field1=CompanyIDCombo.Text
Myquery= field & " " & op " " & " " & field1 'This may not be needed
Adodc1.ConnectionString = "Driver Microsoft Access Driver (.mdb) "App.path &"/Tracking.mdb" '
Adodc1.CommandType = amdCmdText
Adodc1.RecordSource = " Select * From Company & Purchasing Where Company.Comp_ID = Field1 & Purchasing.Purchasing.Purchasing_ID= Field'
Adodc1.Refresh
Ok I know there is a problem with my query itself I painly dont know how to do it I know how to query the database using one table but when two tables or more are required I am totally confused. Also I am using Microsoft Access 2000 for the database and Visual Basic 6.0 service pack 6 to write this program
Thank you for all your help
Json
Alter Table Decimal Query On Access 2000
I try to execute this query: "ALTER TABLE T_HASAR
ALTER COLUMN MHASAR_TUTARI DECIMAL(14,2)" on Access 2000 database.
but the query fails and gives a message "Syntax error in ALTER TABLE statement".
What is wrong with the query? could anyone help me?
Problem With Create Table Query In Access 2000
I am new to using Access in Vb. I am trying to create a table to run a report from(I had several problems getting the data the way I needed,so I tried this). I am using DataEnvironment and Datareport. The report will run once, but if I try to run it a second time, I get the error: "Record Deleted". The SQL from the DataReport is:
SELECT SalaryA3Query.RegHrs, SalaryA3Query.OvtHrs, SalaryA3Query.DblHrs, SalaryA3Query.SickHrs, SalaryA3Query.VacHrs, SalaryA3Query.HolHrs, SalaryA3Query.CommErn, SalaryA3Query.Oth1Ern, SalaryA3Query.Meals, SalaryA3Query.NoPay, SalaryA3Query.Tips, SalaryA3Query.Oth2Ern, SalaryA3Query.MiscHrs, SalaryA3Query.AdjToNet, SalaryA3Query.DeleteRec, SalaryA3Query.PAYCDE, SalaryA3Query.SCHR, SalaryA3Query.EXCFL, SalaryA3Query.CYCLE, SalaryA3Query.PAYEMP INTO SalaryTable
FROM SalaryA3Query
ORDER BY SalaryA3Query.PAYEMP;
And the code from my app is (QUERY1 is drawing from the created SalaryTable):
sSQL$ = "DROP TABLE SalaryTable"
Access.Execute sSQL$ ''Deleting previous table
Call DtaReports.CreateSalaryTable ''creating new table
''I get the error on the line above
SQL$ = ""
SQL$ = "SELECT "
SQL$ = SQL$ & "RegHrsb,"
SQL$ = SQL$ & "OvtHrsb,"
SQL$ = SQL$ & "dblHrsb,"
SQL$ = SQL$ & "SickHrsb,"
SQL$ = SQL$ & "VacHrsb,"
SQL$ = SQL$ & "HolHrsb,"
SQL$ = SQL$ & "CommErnb,"
SQL$ = SQL$ & "Oth1Ernb,"
SQL$ = SQL$ & "Mealsb,"
SQL$ = SQL$ & "NoPayb,"
SQL$ = SQL$ & "Tipsb,"
SQL$ = SQL$ & "MiscHrsb,"
SQL$ = SQL$ & "Oth2Ernb,"
SQL$ = SQL$ & "AdjToNetb,"
SQL$ = SQL$ & "SCHRb,"
SQL$ = SQL$ & "Val(PAYEMP) as PAYEMP FROM Query1 "
SQL$ = SQL$ & "ORDER BY PAYEMP"
DtaReports.rscomSalary.Open SQL$
RptSalary.Orientation = rptOrientLandscape
RptSalary.PrintReport
DtaReports.rscomSalary.Close
What do I need to do to run the report multiple times? Thanks in advance
Catrina
Access 2000 - Building String Of Make-table Query
I have a simple query which returns me 3 fields for given values. I then want to run a make-table query on this query to turn the results into a table. My code is below.
If you scroll down, the bit that isn't working is labelled after 'End of 1st Query / start of 2nd below'. "Run-time error '3131' syntax error in FROM clause" is what I get when you run the final line - DoCmd.RunSQL (SQL)
'sql to find list of parts for highlighted suppliers in From Country To Sell In Listbox (1st query)
SQL = "SELECT SupplierPart.SupplierName, SupplierPart.VauxhallNo, SupplierPart.Price " & _
"FROM SupplierPart " & _
"WHERE SuppplierPart.SupplierName IN ("
intindex = 0
Do While intindex < UBound(ArraySupplierToSellIn())
arrayLength = UBound(ArraySupplierToSellIn())
If intindex = arrayLength - 1 Then 'if it is last value in array don't add comma
SQL = SQL & "'" & ArraySupplierToSellIn(intindex) & "'"
Else:
SQL = SQL & "'" & ArraySupplierToSellIn(intindex) & "'" & ","
End If
intindex = intindex + 1
Loop
SQL = SQL & ")" & " GROUP BY SupplierPart.SupplierName, SupplierPart.VauxhallNo, SupplierPart.Price;"
SelectSuppliersForSelling = SQL
'MsgBox (SQL)
'''''''''''''''''''''''''''''''''''''''''End of 1st Query / start of 2nd below
'THIS IS THE BIT THAT ISNT WORKING...SEE HOW I HAVE BUILT STRING.....
'make table SuppliersHighlightedTop using select query just created
SQL = "SELECT (" & SelectSuppliersForSelling & ").* INTO SuppliersHighlightedTop FROM (" & SelectSuppliersForSelling & ");"
MsgBox (SQL)
DoCmd.RunSQL (SQL)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
Retrieve Data From An Access 2000 Table On A Website.
Greetings:
In use VB6, Access2000
I have an Access 2000 Database on a website with a single table to
collect information from visitors to the site.
It collects request for information.
How do I go about retrieving the information from the site on
a timely basis?
If the DB was local I would use a query with a timer, but have no
clue how to go about it since the table is in the site.
Can someone tell me how to correct this poblem?
Thanks for your responses.
Donald
Print Single Line Of Data From MS Access Report/table/query
Hello,
I currently have a vb project in which I can view reports. What I need is...to be able to have a button on my screen in which I can just printout the last recorded data straight to a printer. I guess I need to know how to query the data through VB and then how to print it straight to a printer.
Sounds simple...but is it?
Thanks!
Append Data To Access Table From External Sybase Table
Hi there,
I have created a table in MS Access using ADOX - identical structure to a table which resides on a Sybase ASE 12.x server on another machine. My goal is to copy the contents of the Sybase table into the MS Access table on my PC. I have been trying to do this for two days now and can't figure it out.
I am using ADO to establish connection to the Sybase database. I can read the table I need, populate a datagrid with it, etc. But I don't know how to copy its contents to my access database.
I have tried the code below to no avail. Any suggestiong would be appreciated. Thanks in advance.
' Definitions
Dim ConnString, strSQL As String
Dim rprobDBConnect As New ADODB.Connection
' Close the remote connection if it's already open
If rprobDBConnect.State = adStateOpen Then
rprobDBConnect.Close
End If
' Setup remote connection parameters
ConnString = "DSN=MYSYB1; Uid=sa; Pwd=blah"
rprobDBConnect.CursorLocation = adUseServer
rprobDBConnect.Open ConnString
strSQL = "insert INTO prob IN 'c:probdb.mdb' select * FROM prob"
rprobDBConnect.Execute strSQL
End Sub
: Export Data From Access Table To SQL SERVER Table Programatically
Dear All,
I have a table in Access. I want to export the data from that table's fields into one field of SQL SERVER database table. for eaxmple.
Let I have a table named A in access. Table A has 5 fields. Also i have a table in SQL SQRVER.Let that table name is B. I want to export data from Access table A fileds into one field of SqlServer table B.
Can you help me plz.
RESOLVED!!! Help - Access Data From One Table And Saves It To Another Table
okay, here i am again...
i have information saved in Table1, now when i do editing or update with these data, i have to save these changes in Table2.
basing from previous codes and same interface i used with the payroll project (had combobox for the student id and upon clicking so, needed data will be displayed on the textbox controls)... here's what i did:
i put these two queries at the combo click event:
Code:
sql = "SELECT * FROM Table1 WHERE studeid =" & cmbid.Text & " "
If rsStudeData.State = 1 Then rsStudeData.Close
rsStudeData.Open sql, acadConnection, 1, 2
Call StudeData ' function for displaying data on the textboxes
Exit Sub
sql2 = "SELECT MAX(acadyear) As maxacad_year From Table2"
If rsAcad.State = 1 Then rsAcad.Close
rsAcad.Open sql2, acadConnection, adOpenDynamic, adLockOptimistic
txtacadyear.Text = rsAcad("maxacad_year") 'this textbox's visible property was set to false since it only serves as a container with my acadyear criteria in the update query
Exit Sub
End Sub
then in my update command, i created this update query... with two criteria (must update data bearing the specified student id and current year)
Code:sql1 = UPDATE Table2 SET field1 = '"& txtstude(0).Text & "', field2 = '" & txtstude(1).Text & "' field3 = '" & txtstude(2).Text & "', Field4= '" & txtstude(3).Text & "', field5='" & txtstude(4).Text & "', field6 = '" & txtstude(5).Text & "', field7='" & txtstude(6).Text & "' WHERE studeid = " & cmbid.Text & " AND acadyear = #" & Format(CDate(txtacadyear.Text), "yyyy") & "#"
acadConnection.Execute sql1
this however, had a prob with it... type mismatch - the debugger highlights whole sql
i suspect it has to be my query for the latest date, i tried setting the txtacadyear's property to visible and found out, no date is displayed - meaning my query for the latest academic year didn't work out???...
any idea? tnx...
everything started out from nothing... everyone started out knowing nothing...
Edited by - newbie2005 on 3/1/2005 9:18:08 PM
Query Linked ODBC Table: VB -> Access -> Linked Oracle Table
Hello,
I am trying to access an Oracle Table through a Linked Table setup in Access 97. I had originally just tried to connect directly to the Oracle table from VB, but that isnt working out like I want it to....the queries are not going through well. I want to do it now having Access act as the middle man in between. This is my code so far:
From my connection module
Code:
MDBFile = "H:DataBlah.mdb"
'Set a reference to the ADO Connection object
Set g_objConnRemedy = New ADODB.Connection
g_objConnRemedy.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & MDBFile & ";
Code:
Set g_objRS = New ADODB.Recordset
csSql = "SELECT ARADMIN_PM___CALL_TICKET.Submitting_Unit__, Count(PM___CALL_TICKET.Ticket_Number__) AS [Call Type-Other] "
csSql = csSql & "From ARADMIN_PM___CALL_TICKET "
csSql = csSql & "Where(((ARADMIN_PM___CALL_TICKET.Call_Origin) =""1"""
csSql = csSql & " Or (ARADMIN_PM___CALL_TICKET.Call_Origin) = ""3"""
csSql = csSql & " Or (ARADMIN_PM___CALL_TICKET.Call_Origin) = ""4"""
csSql = csSql & " Or (ARADMIN_PM___CALL_TICKET.Call_Origin) = ""5"")"
csSql = csSql & " And ((ARADMIN_PM___CALL_TICKET.Create_Date_Time__) >= ""1149120000"""
csSql = csSql & " And (ARADMIN_PM___CALL_TICKET.Create_Date_Time__) < ""1149292800"")"
csSql = csSql & " And ((ARADMIN_PM___CALL_TICKET.Status) <> ""6"")"
csSql = csSql & " And ((ARADMIN_M___CALL_TICKET.Problem_Type) <> ""Misc Tfer Call Int""))"
csSql = csSql & " GROUP BY ARADMIN_PM___CALL_TICKET.Submitting_Unit__"
Debug.Print csSql
g_objRS.Open csSql, g_objConnRemedy, adOpenForwardOnly, adLockReadOnly, adCmdText
Now the above SQL Statement works when in a access query, so thats why I directly copied and pasted it. Is it correct that I am assuming that I can access the linked table as if it were a normal table designed in Access? Also, my connection string for ADO is at least correct, cause I setup a generic table with like two columns just to test and I was able to retrive the data.
Edited by - mlosso on 6/23/2006 8:02:00 AM
Passing Into Fields Of A Table-ACCESS
My code is only supposed to run when MATRIX <> CT and MATRIX <> Aq. But it runs for any MATRIX value. Can anyone see what is wrong with this statement.......
Thank you!
If Me.[checkbox1] = True And Me.MATRIX <> Aq Or CT = True Then
'Do something
Retrieving Table Contents Based On Query Results From Another Table
I'm wondering if it's possible to merge two queries together to give me some records.
What I have is a History table. It contains:
History_ID (pk)
Luser_ID (fk)
Track_ID (fk)
History_Date (text)
So whenever someone plays a song, I..well...I add a record. I don't want to duplicate the track info...'cause there's a lot. I will if I have to, but I'd rather take the hard route with less data storage.
My Track table has: (condensed slightly)
Luser_ID (fk)
Artist_ID (fk)
Album_ID (fk)
Track_ID (pk)
Track_Name (text)
...blah blah blah
I have Luser, Artist, and Album tables that have the Key and a _Name text field.
My query to retrieve all the tracks for a particular user is:
Code:SELECT Track.*,Artist.Artist_Name,Album.Album_Name From Track,Artist,Album WHERE Track.Luser_ID=1 AND Artist.Artist_ID = Track.Artist_ID AND Album.Album_ID = Track.Album_ID ORDER BY Artist.Artist_Name,Album.Album_Name,Track.Track_CDTrack
As you can see it gets the text for the Artist and Album from the other tables.
To get the Track ID's from the History table...well that's too easy to post. What I need to do is get the same info I'm getting from the query above, but for all the tracks listed in the History table, with a primary order of History_Date, followed by the other three.
Before I started coding, I tried to think it through. Before I passed out from the effort, the only idea I had was to get all the track ID's from the History table for the user, say the query result looks like:
Track_ID........History_Date (not shown)
1
3
4
2
6
3
1
(Note duplicates)
Then loop through this recordset and get the track info with a similar query as the first above. I consider this route as a bad idea, so I'm trying to think of how I can inject the History table into the above query. I considered adding something like WHERE Track.Track_ID IN (SELECT Track_ID [from history table for user]), but:
What about the duplicates? Will Access give me the info for every record, even if it's duped?
How would I get History.History_Date in my records?
Or
Should I just duplicate the Artist and Album fields from the Track table to the History table, and just change the above query to reflect those instead? I'd then be storing two more fields..but that's ok with me, I reckon.
Or
Since a majority of the poeple looking at this post probably have superior database knowledge than myself, I am open to radical, deviant, or any other suggestions I can not think of.
I'm attaching a sample database for your convienence. Access 2000. Thanks if you can help!
Edited by - Blackened on 6/26/2005 7:44:55 PM
Access 2000 Updating A Table
I have a form where the comments are updated and added to its own table. I Can't seem to remember how to write the statement to do this.
Dim Version As Integer
Dim MySQL As String
Dim DB As Database
Dim Rst As Recordset
Dim StrComments As String
Dim MySql2 As String
Version = Me.Txt_CommVer.Value
Criteria = Me.txt_GroupNumber.Value
StrComments = Me.txt_GroupComments.Value
MySQL = "SELECT * FROM GroupDetail " & _
"WHERE ((Comments.Comments)='" & StrComments & "'); "
MySql2 = "Insert Into Comments (GroupNum, CommVer,Comments) Version, Criteria, StrComments;"
DoCmd.Hourglass True
Set DB = CurrentDb
Set Rst = DB.OpenRecordset(MySQL)
MyCtr = Rst.RecordCount
If MyCtr = 1 Then
MsgBox ("You haven't updated any comments")
Me.txt_GroupComments.SetFocus
Else
'Insert into statement here
Docmd.runSQL (MySQL2)
End If
End Sub
Protecting One Table In Access 2000
In an Access 2000 database, is it possible to protect only one table with a password? There should not be any password to open the mdb file.
Make Table Using ADO And Access 2000
I keep asking this question and I havent found an answer yet.
I am using Access 2000 connected to an Oracle backend. I want to hit the backend, pull the records I need into a table then process reports against those records.
So far I have been unable to figure out how to do that. I can not seem to get a query to execute using ado.
Please just submit some coding examples. This is work relate, I am unable to post my code, and when I have in the past and tried to take out pertainant info, I seem to make typos.
so please, examples or links and I can sort thru what I need to assist me
thanks,
Swoozie
IMporting A Csv Or Txt Into Access 2000 Table
Can anyone help?
I am searching for some code that will allow me browse for an import a csv or text file into a table within access 200 database. I use 2 tables which have different amount of field in so I need to be able to specify how many fields will b imported. The files that are being imported always have the sam amount of fileds in ie csv1=20 fileds cv2=32 fields etc
I will be changing from Access 2000 to SQL Server soon. Will th cpde be the same for both?
I'm not having any look find code for this and so am getting bit desperate (aaaahhhhhhh poor me) Any help will be greatfulll appreciated.
Create A Table In Access 2000
I am using vb6sp5 and Access 2000, I run the program and it works fine right up until it is supposed to create a table. It gives me this error:
"Runtime error 3292 syntax error in field creation"
It removes the existing table with kill(tablename) but it will not create a new one. I have supplied the following code for you review.
It fails on this line :(Call fNewDB.Execute(SQL) 'executes the table creation)
any help would be appreciated.
Private Sub cmdNewTest_Click()
Dim FileName As String
Dim SQL As String
Dim fNewDB As Database
Dim fUserWS As Workspace
Dim fquizDB As Database
Dim fQuizrs As Recordset
Set fUserWS = DBEngine.Workspaces(0) 'sets default database workspaces
FileName = AppendFileToPath(App.Path, "questions.MDB")
If FileExists(FileName) = True Then 'verifies that there is not an existing data file
If MsgBox("The Questions database already exists. " & _
"Do you want to delete the current database?", vbYesNo + vbQuestion) = vbYes Then
Kill FileName
Else
Call MsgBox("You have decided not to create a new database at this time." & vbCrLf & "Please run the application again to make another selection")
End
End If
End If
'Creates a new database
Set fNewDB = fUserWS.CreateDatabase(FileName, dbLangGeneral)
SQL = "CREATE TABLE questions ("
SQL = SQL & "qNum TEXT(50), "
SQL = SQL & "Question TEXT(255),"
SQL = SQL & "Answer_a TEXT(50),"
SQL = SQL & "Answer_b TEXT(50),"
SQL = SQL & "Answer_c TEXT(50),"
SQL = SQL & "Answer_d TEXT(50),"
SQL = SQL & "Answer_e TEXT(50),"
SQL = SQL & "Answer_Correct TEXT(50),"
SQL = SQL & ")"
Call fNewDB.Execute(SQL) 'executes the table creation
'Load frmDataEntry 'loads the data entry form
frmNewTest.Show 'shows the data entry form
'Unload frmStart 'unloads the startup form
End Sub
Saving An Access 2000 Table To CSV
Hi All,
Do any of U know how to export an Access 2000 Table to CSV from VB6. At the moment I am just using a recordset and a loop to write each record using the write #1 statements but it takes ages. Can anybodyt hink of a quicker way ?
How To Change A Field In Table (Access 2000)
I use the following SQL:
ALTER TABLE MEMBERS MODIFY m_password TEXT(32)
but I get a error: "Error syntax..."
And then, I try with
ALTER TABLE MEMBERS MODIFY m_password CHAR(32)
but i still get the same result
How i have to do?
Access 2000 Create Table SQL Questions
Hi,
I am trying to create tables in Microsoft Accesss 2000 using the CREATE TABLE function in sql. I have a couple of questions about using this type of statement.
Using the script below as an example:
Q1, How do I make one of the fields the primary key, say "productno"?
Q2, How do I make "productno" an "autonumber" data type?
Q3, How do I make "Status_New" an "Yes/No" data type?
Thanks
Slim
Code:
CREATE TABLE [TB - Product Formulas]
(
productno number,
Product_Code text(50),
Dim_Code text(50),
Product_Description text(50),
Manufacturer_Code text(50),
Fcode1 text(50),
Fcode2 text(50),
Fcode3 text(50),
Full_F_Code text(50),
Label text(50),
Formula text(50),
Status_New text(50),
Status_Draft text(50),
Status_Quote text(50),
Status_Abandoned text(50),
Date_entered date,
Entered_by text(50),
Comments memo
);
Create Table Script In Access 2000
Hi,
Does anyone know if it is possible to create tables in access 2000 using the following script, but passing the table name as a parameter i.e. instead of "CREATE TABLE TB_Temp", use "CREATE TABLE [MYPARAMETER]".
At the moment the VB application just runs the script stored in the access 2000 database, but of course I would like to pass the table name as a parameter. The reason I would like to do this is so the users can save temporary data in the database for later and I thought I would create a temp table for each user i.e. "TB_TEMP_[USERNAME]" and then delete it when it is no longer used.
Any ideas, or is there a better way to do this???
Slim
Code:
CREATE TABLE TB_Temp
(
Product_ID text(50),
Formula_Reference text(50),
Product_Description text(50),
Tablet_Colour text(50),
Tablet_Type text(50),
Weight text(50),
Shape text(50),
Hardness_or_Fragility text(50),
Disintergration_time text(50),
V_or_VV text(50),
GM_Status text(50),
Daily_Intake text(50),
Organic_Status text(50),
Shelf_life text(50),
Irradiation_status text(50),
Barcode text(50),
Supplier_ID text(50),
Ingredient text(50),
Ingredient_desc text(50),
Label_claim text(50),
A_or_E text(50),
Tech_ing_data text(50),
Content_unit_mg text(50),
Supplier_ref_code text(50),
Purchased_by text(50)
);
Error Updating Access 2000 Table From VB6
I'm having problems updating an MS Access 2000 memo field from VB6.
If the Memo field (PROGRESS_NOTES) is empty everything is fine. When it has data the problems start.
Also, Everything works fine when I enter the SQL Statement right into Access and execute, when I fire it from VB, it blows up and gives me the following error.
Error #6 - Overflow
Here is a sample statement that makes it crash.
UPDATE tblCHANGE_REQUEST
SET PHASE = 1,
REQUESTOR_ID = 9,
TRANSRULE_DB01 = 1,
TRANSRULE_DB02 = 0,
STATUS_TYPE = 'START',
ASSIGNED_ID = 3,
PRIORITY = 'HIGH',
TYPE_OF_FIX_ID = 2,
RELEASE_ID = '2.2.2',
PROGRESS_NOTES = '4/2/2004 Project Plan Mtg: Update CDW Rls = May 2004. Exp. Close Date from 4/30/04 to 5/31/04.
4/1/2004 Some issues. Go or no go will be decided on 4/2.
2/13/2004 Leave as is.
1/22/2004 Phase 1 will be put on hold because user (Deb O.) wants to incorporate work with the historical deliverable. Updated Status from Dev. to Start; Exp. Close Date from 2/27 to 4/30.
1/15/2004 Data being reviewed by users.
1/13/2004 Rls Mgmt Mtg: Will complete this task in phases starting with Phase 1(Incremental). Phase 2 (Historical). Updated CDW Release from TBD to 2.1.3; Exp. Close Date from 1/30/02 to 2/27/04.
12/11/2003 Meeting occured. In the anlysis phase.
12/4/2003 PS will be meeting with JZV, Deb O, and Chris C. to discuss the type of service logic and deliverables for CR166, 150 and 105.
9/8/2003 Updated CDW Release from Oct. to TBD; Expected Close Date from 10/30/03 to 1/30/04.
7/21/2003 Release Mgmt Mtg: Updated Expected Close Date from 9/30/2003 to 10/30/2003, and CDW Release from September to October 2003.
5/28/2003 DSC Mtg: Updated Expected Closed Date from 6/30/03 to 9/30/03.
5/8/2003 ICD9CM issue is closed. Request is ON HOLD
3/20/2003 No update
2/20/2003 No update
2/6/2003 Updated Expected Close Date from 12/31/9999 to 6/30/2003 (2nd QTR).
1/30/2003 Consolidation with CR83 is questionable. Updated status from PRE-ANALYSIS to DEVELOPMENT (for Awaiting Development) .
1/9/2003 Consolidate with CR83.
11/20/2002 83 Must be completed before 105 can be worked on.
11/06/2002 DS re-prioritized as HIGH (was MEDIUM).
10/23/2002 Defined list of changes (PK).
10/02/2002 Updated status to In Analysis.
07/31/2002 DS re-prioritized as MEDIUM. Changed In Analysis (ETL). Changed Status to Pending.
07/22/2002 Data Stewardship is currently re-prioritizing request.
07/10/2002 Requires other fixes to be completed before this can be accomplished. Also may duplicate parts of other change requests already submitted. Will be sent to DS. Updated to status to DS Council Prioritization.s other fixes to be completed before this can be accomplished. Also may duplicate parts of other change requests already submitted. Will be sent to DS. Updated to status to DS Council Prioritization.'
WHERE CHANGE_ID = 105;
How To Read MSysRelationships Table In Access 2000 ?
Hello All,
How to access or read MSysRelationships table in ACCESS 2000 through Vb code ? coz when I code rsrelationship.open
VB Code:
"SELECT COUNT(*) AS Relation FROM MSysRelationships,de.connection1,adOpenStatic,adOpenReadOnly,adCmdText"
I get error message attached with this thread.
Thanx in advance. waiting 4 +ve reply
Retrieve ACCESS 2000 Table Relations
Hi,
I want to retrieve through my VB application the table relations from an MS ACCESS 2000 database. I want to know which field of a table is a foreign key and to which field of whom table is connected. My program must run select queries on any .mdb file and I have to know if the sql that the user creates through a Wizard is correct and gives correct results.
I guess this will be achieved by OpenSchema method, but how exactly?
Sorry for any bad english, :)
Resizing A Table In A Subform In Access 2000
I have a table that I am using as a sourceobject for my subform. However, the caption in one of the fields is being cut out and you can't really see what it is. Is there any way I can resize this through code?
Deleting A Temporary Table ... In Access 2000
Hi.
As usual a complicated question .... I create a temp table keyed to a specific userID ... This table is passed to at most 3 related forms ... In the last form I want to delete that table. The DoCmd.DeleteObject will not work because the table is used in another process ... This happens even after closeing the 1st two forms programmatically from the 3rd ... Any ideas on how I can do it?
Sequence of events:
1. Open form #1 and create temp table
2. Call form #2 from #1 and pass the temp table name to it
3. Call form #3 from #2
4. Form #3 closes forms #1 and #2 and attempts to delete the temp table and fails ...
Thanks!
- Mike
How To Split And Combine Data In A Table (MS SQL 2000)
Hi all,
I hope I may get a solution for this as early as possible.
We are working in a project where scanned documents are going to store in MS SQL 2000 database. There is only one table and its growing as 2 to 3 GB per day. For safety reason, we are thinking of splitting the data of the table according to one key field book_no.
Please help me how to split the table with the query like "SELECT * FROM doc_master WHERE book_no=1" and split the table for different book numbers and later combine them to form original table.
Please help me. Its urgent.
--
Regard,
Rajeev Vandakar
Need Help In Microsoft SQL 2000 Upon Inserting Data From Another Table
Elow there it's me again...I really need help badly..I have already display on my listbox but what happen to my display is that i want to insert data if it is not found from the table i call from the display..Is it possible?
Here is my screenshot:
the first row there states that my table dont have code and particulars...What I want is that i want to fill in the blank and call another table wherein the first 4 number of glcode was found in another table..I want to insert from that listview...How can I do that? Could anyone help me with this problem please....
Unable To Add New Record To Linked SQL Table In Access 2000
I have a table linked to my Access 2000 project. I am unable to add a new record to this table.
Allow Additions is set to Yes on my form. I have all rights to the SQL table.
I went to the Update web site for Microsofit Office 2000 and ran both upgrades for Office 2000. My operating system is Windows 2000.
My MDAC version is 2.6.
Does anyone know why I am unable to add new records to my linked SQL table.
Test Path Before Linking Table - Access 2000
hi
i'm relinking tables in code but first i want to test the strPathName given actually exists, its possible that the table itself is not linked initially (hence the relink) so i do not want to open the old linked table as a test.
Code:
tbl.Properties("Jet OLEDB:Link Datasource") = strPathName
would i test the jet link above, or would i test the strPath using API or another method?
many thanks.
Edited by - AbbydonKrafts on 11/17/2006 6:54:38 AM
VB App/Access 2000 - Create Table - Unspecified Error
I have a VB App that inserts rows into an Access 2000 database. I am using an ADO Command object with a concatenated string as the command text (INSERT INTO...). I run perfectly in the development environment. When I compile and test the EXE, I get the Error (-2147467259). Unspecified Error. I am triple/quadruple sure that I am cleaning up after myself, i.e. Closing connections and Setting to Nothing for all of my ADO objects.
Question1. I've read in one of these forums about an errors collection. Is there more information available pertaining to the 1 Error I receieved? If so, how do I attain that information?
Question2. How do I solve this problem?
BruceH
Bulk Update Access 2000 Table Values
Hello,
I would like to update the majority of the values in a field of a table. The data I would like to update it with is available in an imported table. Let me explain. I have a table with mobile phone details (let's call it mastertable). One of the fields is a userID, but this field is empty. The imported table contains the majority of these UserIDs and also the mobile number. What I would like to do is loop through my mastertable and compare the mobile numbers. Once it matches, it should update the master table its UserID field.
I already tried this via a update nested Query, but I did not manage to get it working. But I think a 'simple' query can't do the job for me this time. So that's why I post a message in the VBA area of this forum. Does someone have an easy to understand piece of code that can easily be adjusted to solve my problem? It should be progressed once.
Thanks in advance for your reply!
Edited by - Superbuis on 12/15/2007 2:42:51 AM
Adding 5 Blank Rows To A Table In Access 2000
Hi.
This is a rather unique question ... I was wondering if there was a way to insert 5 blank rows into a table in Access. This table has no key field so no problem there ... Just wanted to avoid the loop and insert action query ... Thanks.
- Mike
P.S. - I'll probably come up with an answer quickly ....
VBA In MS Access 2000, Problem Adding Entry To Table.
hey, I'm currently in the process of making a project in Access where I have to use forms to add, delete and read entries from a DB. I'm stuck tryin to make a command button add an entry to the database, I have the form displaying the current contents of the database in a list box and I wish to enter the contents of a text box into the table directly under the last entry via a command button and then save the table. I think the form will have to be closed and re-opened to stop getting the read only error as the data is being used by the form in it's display. Hope that helps anyone willing to spare their time in assisting me, you'll get a mention as will your web site url in my bibliography section of the project.
I have experience with VB in which I made an IRC client and various text editing and formatting tools, unfortunately I'm relatively new to Access 2k, only ever used VBA modules in Excel for last yrs project.
Thanks in advance, I hope for some helpful resposes.
Get Access Table/query Records
In a VB 6.0 application, I am trying to get the selected records from a table or query using the following method:
Code:
Public Function GetTableRecords(ap_Application As Access.Application, TableName As String, CurrentRow As Long, SelectedCount As Integer) As Object
Dim lobj_Table As Object 'Access.Form
Dim li_Return As Integer
li_Return = ap_Application.SysCmd(acSysCmdGetObjectState, acTable, TableName)
If li_Return = 0 Then
'Object is not open
Set GetTableRecords = Nothing
Exit Function
End If
'Make the table active
ap_Application.DoCmd.OpenTable TableName
Set lobj_Table = ap_Application.Screen.ActiveDatasheet
If lobj_Table Is Nothing Then
Set GetTableRecords = Nothing
Exit Function
End If
CurrentRow = lobj_Table.SelTop
SelectedCount = lobj_Table.SelHeight
If SelectedCount = 0 Then
SelectedCount = lobj_Table.RecordsetClone.RecordCount
End If
'DAO recordset for MDB, MDE, MDA
'ADO recordset for ADO, ADE
Set GetTableRecords = lobj_Table.RecordsetClone
Set lobj_Table = Nothing
End Function
This works great for MDB, MDE, and MDA files. Users can sort records, filter, and I always get the expected results.
However, this method doesn't work for ADP, ADE files. I get the original recordset as it was retrieved from the server.
So assume the user resorts the table in Access by clicking on the primary key column then selecting Records-Sort Descending (the records are now in reverse order). When I look at the recordset returned by the ActiveDatasheet, I do not get the newly sorted recordset. So when I see that the users first selection was row 1 and the selected count was 3, I get the first three records in the original table as stored on the server, which are now the last three records in the display. What I need is to get the first three records that are being displayed on the screen, the records that the user has actually selected.
Is there any object that will allow me to do this? Any clever workaround?
Access Top Values In Table Or Query
Hello
I have an Access query that has several origin and destination points and the cost associated with each one.
For example I may have 8 occurrences of Cleveland to Chicago, all with different costs because different companies are involved. I want my query or form to show only the top 4 cheapest for each route.
I have my query sorted in ascending order on the cost field, but I do not know how to show only the top 4 values. I tried using the Top Values drop down box on the toolbar in query design view, but that only gives me the first 4 records.
I am not sure if there is a function in Access to perform such a thing or if I need some VBA to accomplish this.
Any help would be much appreciated.
Thanks
Ms Access VBA Query - Table Relationships In VBA
Hello to all,
I'm in need of some help with a VBA query. I've never been able to understand how to sum a column from more than one table.
I have attached a simple MDB which has four Product tables & a Product List table. The idea is to sum the cost field in each of product table but firstly checking to see which tables to include by looking up the Product List table. The added confusion is that I have added checkboxes to the Product List Table. So that I can keep a full list of products but check the ones I require for query.
Can anyone help. Please
Rak
Group BY Query Access Table And Vb
when I run this query from vb to access table in access:
pstrsql = "select Account, Name, City, State, Region,count(account) AS [Account_Total] from mytable where dataid =" & dataid & " and aop = " & Aop & " group by account order by count(account) desc; "
I get this error:
"You tried to execute a query that does not include the specified expression 'name' as part of an aggregate function"
But it works when I exclude the rest of the fields like name, city etc..
pstrsql = "select Account, count(Account) as [Account_Total],name from mytable where dataid =" & dataid & " and aop = " & Aop & " group by Account order by count(account) desc; "
The rest of the fields are text fields.
can some one please help? How can I include the rest of the fields like Name, city state, region in the query and group them by only Account.?
Thanks in advance.
Copying An Access Query To An SQL Table
I have an Access database that contains a query; I want to copy all the data of that query to my SQL Server table. The query does not have any ID numbers or unique field; I don't control the database so I can't change it.
In my SQL table I have ALL of the query's fields plus my primary key fields of ProjectID, SnapshotID, and ID. ProjectID and SnapshotID are static values known at the start; the ID field is just a number from 1 to whatever to uniquely identify the record.
Now I know I can do a INSERT INTO ... SELECT statement to copy all the data over, with the exception of the ID filed. How can I INSERT the data with an appropriate value for the ID field when there is no ID field in the original Access database?
Join SQL And Access Table In VB Query.
Hello all!
This should be simple but I can’t figure it out...
I have a table in SQL and a table in Access that I am trying to join. I have not found any way to do this so I creating two recordsets and manually searching through the records for a match and then created a 3rd record set with the appropriate records. This method does work, however it is VERY slow.
Does anyone know how to join an Access and an SQL table?
An alternative would be to link the SQL table in Access but the SQL table uses SQL authentication and therefore requires me to pass the login name and password. I would also rather not have to create an ODBC connection on every PC i install my software on. Thanks!
Query Result Different Data To Table?
Hello,
I'm programming in VBA For excel.
my question is . Is it possible to perform an SQL query that replace's values in records when ran?
i.e
if i have a table with a "FaultCode" field which is full of numbers like 01 02 03 04 etc, i want to select where faultcode = 01 but instead of 01 i want it to read "HT"
a new field would also do the trick, i.e
SELECT Name, FaultCode as Whatever FROM metable WHERE FaultCode = 01
can i make whatever read HT all the way down?
I can't modify the data in anyway, not going into it.
any help appreiciated.
Bob
|
TRACKER
