Add A Field To Query Via Code (vb6 & Access)
Hello
I need to make a recovery program to add a field to a query in users databasae.
This query made from 2 tabels.
Can I do it with code?
for example a madule that check query for this field and if there wasn't , add it to query automaticaly?
Thanks
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Update Table In M.Access,code Field,query
Hi
I have made an application to connect Microsoft Access with Visual Basic 6 to save some records!!!! My table in Microsoft Access has these fields:
code ->>>>>>>>>integer
Name ->>>>>>>>>>char
LastName ->>>>>>>>>>>>>char
Age ->>>>>>>>>>>>>>>>integer
Now i save the records correct to my table!!!!In code field count my records!!!
for examle: 1 Jim Dean 29
2 Leite Koontz 40
3 Melina Barker 20
4 Elina Smith 50
Now when i delete a record for example 3 row( Melina Barker) and save new record in code field will take number 5 but in table i have 4 records saved!!! How i can correct that????? With query??? But how??? Any code ideas????
Query For MS-Access: SQL Differences In Access Query Window Vs. Using ADO In Code
OK, many of us have come across the "Like" wildcard issue. When doing an Access query in Access itself, the "*" is used, whereas doing the query thru ADO in code, the "%" is used.
What other situations are there like this? I have a query that uses IIF, INSTR, and UCASE. When I test it in the Access query window, it works fine, but when I produce the same SQL dynamically in my VB program and try to execute it (using ADO), it chokes. Obviously, "standard" SQL doesn't like these VBA functions in the query. Does anyone know (or know where I find documentation on) the specific differences when doing queries for Access in the Access IDE vs. ADO? Thanks.
MS Access VBA: SQL Query -> ADD Autonumber Field
Hi,
i'm new to coding in VBA and Microsoft Access, so the problem probably is easy to solve
I do the following query and get the table you see underneath:
Hi,
ich mach mittels folgendem SQL Befehl eine query in meinem visual Basic zusatz Programm zu Microsoft acces:
Code:
strSQL = "SELECT ModifiedTables.ModifiedTableID, Tabelle1.Act_Author, Tabelle1.ModificationNumber, ModificationLog.Type " & _
"INTO UserInteraktionen" & i & " " & _
"FROM ModifiedTables RIGHT JOIN (ModificationLog RIGHT JOIN Tabelle1 ON ModificationLog.ModificationNumber = Tabelle1.ModificationNumber) ON ModifiedTables.ModifiedTableID = ModificationLog.ModifiedTableID " & _
"WHERE (((Tabelle1.Act_Author)=[Benutzername]));"
http://home.pages.at/gxesch/UserInteraktionen1.Tab.jpg
my problem is:
the ModifiedTableID field is on "autowert" (="autonumber" in english). Does this mean that MS Access uses this field as Primary Key?
I need a new field which adds autonumbers to each record and it can be used as primary key.
When i try to add such a field, i get an error telling me, there already is a "autonumber" (autowert) field.
How can i solve this?
thanks a lot in advance!
PS: Sorry for my not perfect enlish ;-)
Access 2000 Run Query For Nothing In The Field?
Is it possible to run a query and bring up all the records that have nothing in that field. I tried using this: = "" but it still doesnt bring up those records that have that field blank.
Thanks Nick
Access: Query With 1 Field No Duplicates
Morning everyone,
Question is this....I need to query my Access Database. 1 table with tracking (Everything that has happened to a specific record (Edits) and date time) How do I query this database so it pulls up only the most current record.
So if there are 4 edits to record 1....it will only pull up the newest edit.
if anyone needs more info, please let me know.
Thanks
Access: Update Query Sets My Field To -1
Good evening,
I'm running a VBA code on Access form, i have a weird result from an Update query, instead of the needed results, it sets the filed to -1.
the query is
Code:
UPDATE ps_Transaction
SET StrategyID= 'F03293_20070802' AND Checkbox=No WHERE TF_TransactionID = '1001646' and ID='1658850-CSALCOCOEUR'
this query sets the field StrategyID to -1 instead of 'F03293_20070802'
My field is a string, and the size of the field is big enough to hold the new data. what could be the problem?
thanks,
Mimi
Running An Access Query Using A Form Field For Criteria
Scenario:
Access 2000 DB with several Link tables pointing to a SQL DB. Link via ODBC. Access Form gets input for query criteria.
Need to use VB to output the results of a query to MS Word. The Access report and associated query work great. However, my VB code will not open the query without an error.
My sequence of events is:
User sees Access switchboard, navigates to the button for the desired report. That button opens the form, frmRMABill. the OK command button on this form calls the following module:
Sub RMABilling()
Dim objWord As Word.Application
Dim doc As Word.Document
Dim rstPartBilling As ADODB.Recordset
Dim rstLaborBilling As ADODB.Recordset
Dim rstHeadBilling As ADODB.Recordset
Dim cn As ADODB.Connection
Dim qryPart As String
Dim qryHead As String
Dim qryLabor As String
Dim partTotal As Single
Dim hourTot As Single
' Open Word Document
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set doc = objWord.Documents.Add("h:RMAbilling.dot")
' HEADER QUERY
qryHead = "SELECT dbo_RMA.ID, dbo_RMA.NEW_CUST_ORDER_ID, dbo_DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID, dbo_RMA.IS_WARRANTY, dbo_RMA.LAST_RECEIVED_DATE " & _
"FROM dbo_RMA LEFT JOIN dbo_DEMAND_SUPPLY_LINK ON dbo_RMA.NEW_CUST_ORDER_ID = dbo_DEMAND_SUPPLY_LINK.DEMAND_BASE_ID " & _
"WHERE (((dbo_RMA.ID)=[forms]![RMA_NO]![RMA]));"
Set cn = CurrentProject.Connection
Set rstHeadBilling = New ADODB.Recordset
With rstHeadBilling
.Source = qryHead
.ActiveConnection = cn
.CursorType = adOpenStatic
.LockType = adLockPessimistic
.Open Options:=adCmdText
End With
I am wondering if I need to address the criteria field in my input form witihin the code for this module or if my order of events is wrong. Compiler gives me an error:
No value given for one or more required parameters.
Any suggestions are greatly appreciated.
Thanks,
Sorting An Access Query Based On A Form Field
Hello everyone!
I have an access database for which i built a search form where you enter your criteria and it then runs a query and displays the results. Everything works great except I am trying to add a "sort by" option onto the form, but I cannot get it to work. I am trying to have the ORDER BY clause of the query's SELECT statement reference a control (which contains the names of the columns on which you can sort by) on the form. However, it doesn't seem to be working, and i am almost certain i have something wrong with the ORDER BY clause. Here is what i started with:
ORDER BY [Forms]![Search]![cboSORT_BY];
The form is named "Search" and the control I am trying to reference is a combo box named "cboSORT_BY." I have the form set up to requery the results when the value is changed.
I am figuring that im just missing some stupid syntax error.
Access 2003 Query Sort Field Non Ascending Or Descending
Hi everyone.
I have a select query to pull a field [Lockbox] from a table and sort it in a specific order (non ascending or descending).
The problem is, when there are no records with Lockbox it errors out, but when the are (unprocessed) Lockboxes it runs fine.
The SQL code is as follows:
SELECT tblGC100.Lockbox
FROM tblGC100
GROUP BY tblGC100.Lockbox, tblGC100.[User Name], tblGC100.[Date Stamp], tblGC100.[Time Stamp]
HAVING (((tblGC100.Lockbox)<>"0002691") AND ((tblGC100.[User Name]) Is Null Or (tblGC100.[User Name])=GetCurrentUserName()) AND ((tblGC100.[Date Stamp]) Is Null) AND ((tblGC100.[Time Stamp]) Is Null) AND (([Forms]![GC100 Data].[txtDate])=[tbl_GC1002].[Cash Deposit Date]))
ORDER BY IIf([Lockbox]="0406919",1,IIf([Lockbox]="0406946",2,IIf([Lockbox]="0406955",3,IIf([Lockbox]="0406968",4,IIf([Lockbox]="0406927",5,IIf([Lockbox]="0406979",6,IIf([Lockbox]="0406933",7,IIf([Lockbox]="0406990",8,IIf([Lockbox]="0409548",9)))))))));
My theory is that I didn't account for the Null value in the IIf statements which is returned when the criteria is not met.
What would I add to the IIf statements to account for this Null value?
Thank you and have a great weekend, Wheels
Edited by - wheels on 5/23/2008 12:50:19 PM
[access Vba]Problem With An Insert Query In A Memo Field: Run-time Error 3075
Hi,
I've a problem with a simple insert query when the number of characters inserted into a memo field is hight.
this is my code:
Code:
...
Dim fso As New FileSystemObject, ts As TextStream
Dim s As String, sql As String
Dim db as Database
Set db as CurrentDb()
Set fso = CreateObject("Scripting.FileSystemObject")
For i = 1 To selFiles
Set ts = fso.OpenTextFile(.SelectedItems.Item(i), ForReading)
s = ts.ReadAll
ts.Close
sql = "INSERT INTO documenti (lid, dtitle, dtext, ddata) VALUES (" & _
Me.lang.Column(0) & ", '" & _
Replace(nof(.SelectedItems.Item(i)), "'", "''") & "', '" & _
Replace(s, "'", "''") & "', '" & _
Now() & "')"
db.Execute (sql)
Next i
I read a text file and I insert its contents into my database, but when the length of variable 's' is more than 18/20,000 chr I got runtime error 3075, but a memo field could contain up to 65,000 chr ca.
Anyone could suggest me where is the problem?
Set Field Values Using VB Code In Access
I need to set field values in a report. i would like to use vb code to do so. I put the code on the detail 'on print' event procedure, and i'm receiving error messages. Is this possible, or is this only used to set field properties?
How To Create AutoNumber Field On Access Db In VB6 Code
i create a table in vb6 code on access db by this statement:
"CREATE TABLE T_HASAR_EK_RAPOR (ID LONG, TONAY_NO CHAR(20), DEK_RAPOR_TARIHI DATE, TEK_RAPOR_ACIKLAMA MEMO, TEK_RAPOR_NOT MEMO)"
but i want ID field to create auto number. which key must follow 'ID' field to make the field autonumber?
anyone help?
How To Put Current Date In To Access Field From VB Code?
Hello All,
I am Having some trouble putting Date value in to a Access DB file with VB6.0.
Here's what my code (Just for the insertion part) looks like. Based on a timer interval, I am trying to write the date value (short format)in to my Access DB
table Named "DAQ".
------------------------------------------------------------
Private Sub Timer1_Timer()
Set DBX = New ADODB.Connection
DBX.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:My.mdb"
DBX.Open
DBX.Execute ("Insert Into DAQ(LDate) Values('Date', 'my data')")
DBX.Close
End Sub
---------------------------------------------------------------------
I tried formatting my access 2000 column "LDate" with various date formats without any success. Can somebody point out what I am doing wrong here and may be help out with the proper syntax for doing this?
I can get Access to write in the date automatically by setting the field parameters thru Access each time I send "my data" to the DB (without using "Date", or "Date()" or "Now"). But I want to learn how to add the date to a Access DB field with VB code.
Thanks.
SkyFox
Access 2003 – Change Of The Field Size Via Code
In many tables I need to change field size from double to single.
In the code below highlighted line does not work:
Code:
Sub ChangePropertiesOfTable(sNameOfTable As String)
Dim objTblDef As TableDef
Dim objField As Field
Dim objDB As Database
Set objDB = CurrentDb
Set objTblDef = objDB.TableDefs(sNameOfTable)
For Each objField In objTblDef.Fields
If objField.Type = dbDouble Then
objField.CreateProperty.Type = dbSingle
End If
Next
End Sub
Can anyone help?
Changing The Field Size For An Access Table Using VB Code
I am accessing an Access database using Visual Basic. I need change the field size AND type of certain columns. My code generates the same error when tyring to change both:
"RUN-TIME ERROR '3219': INVALID OPERATION"
The following code snippet shows how I'm trying to do this.
NOTE: Access uses numbers to indicate type: 7 = number, 10 = text. You'll see this in my code.
If anyone can help, I'd appreciate it.
Thanks,
Mark
=========================
Private Sub AdjustFieldSize_Click()
Dim oAccess As Access.Application
Dim rst As Recordset
Dim h, i, tablecount As Integer
Dim tablefound As Boolean
Set oAccess = New Access.Application
oAccess.OpenCurrentDatabase sDatabaseName
.
.
.
If tablefound = True Then
For i = 0 To 40
If oAccess.CurrentDb(h).Fields(i).Name = "regnum" Then
oAccess.CurrentDb(h).Fields(i).Type = 10
oAccess.CurrentDb(h).Fields(i).Size = 200
End If
Next i
End If
Exit Sub
oAccess.CurrentDb().Close
Set oAccess = Nothing
End Sub
Access VBA Code In SQL Query
Is it possible to open a query from MS Access using ADO where the query contains vba code from the Access macro?
Code:
SELECT Null AS PanelDetailID, Null AS PanelID, "" AS PanelNo,
tblComponents.Width, tblComponents.MinLength,
tblComponents.MaxLength, "N" AS Required, tblComponents.Value, 9999 AS
ProductionOrder, ZoneByLength(tblComponents.TypeID,0) AS [Zone],
"" AS PrintString, IIf(tblComponents.FingerJoint=True,"Y","N") AS FingerJoint
, IIf(tblComponents.Remnant=True,"Y","N") AS Remnant
ZoneByLength is a function within the VBA code.
Create New SQL Code For A Query In Access?
I am generating a report based on a query. The query prompts the user for a product type. Here is the current sql for that query:
Code:
SELECT Contacts.[Company Name], Contacts.[First Name], Contacts.[Last Name], Contacts.[Phone Number],
Contacts.Extension, Contacts.FAX, Contacts.[Product Type], Parts_For_RFQ.[Part Number],
Parts_For_RFQ.[Ann Vol], Parts_For_RFQ.[Model Year], Parts_For_RFQ.Platform, Parts_For_RFQ.Quote,
Parts_For_RFQ.Misc, Parts_For_RFQ.[Due Date], [First Name] & " " & [Last Name] AS WholeName
FROM Contacts, Parts_For_RFQ
WHERE (((Contacts.[Product Type]) Like [Enter Product Type for Filter (leave blank for entire preferred list)]) AND ((Contacts.Preferred)=True))
ORDER BY Contacts.[Company Name];
Note the section where is has the prompt:
Code:
WHERE (((Contacts.[Product Type]) Like [Enter Product Type for Filter (leave blank for entire preferred list)]
Now, in a userform, I would like the ability to select the product type and have that query use the product type from my combobox. How can I (through code) modify the query to reflect the new SQL? Something like:
Code:
SELECT Contacts.[Company Name], Contacts.[First Name], Contacts.[Last Name], Contacts.[Phone Number],
Contacts.Extension, Contacts.FAX, Contacts.[Product Type], Parts_For_RFQ.[Part Number],
Parts_For_RFQ.[Ann Vol], Parts_For_RFQ.[Model Year], Parts_For_RFQ.Platform, Parts_For_RFQ.Quote,
Parts_For_RFQ.Misc, Parts_For_RFQ.[Due Date], [First Name] & " " & [Last Name] AS WholeName
FROM Contacts, Parts_For_RFQ
WHERE (((Contacts.[Product Type]) Like "Clamps") AND ((Contacts.Preferred)=True))
ORDER BY Contacts.[Company Name];
Can't Call Access Query W. VBA Code From VB?
I have an Access 2K query that call a VBA function stored in a module in Access. The query works fine when I execute it within Access. When I try to execute it from a VB program, I get an 'Undefined function FunctionName in expression'. How can I make FunctionName visible to my VB program?
Editing A Query In Access By VB6 Code
in order to get word report from access in my vb6 program, i create a template query in access's queries tab. vb6 code uses this template query. and i distributed this access file lots of users.
problem is here: i have to add some fields into this query. how can i edit this template query in access database by using vb6? (i must do this process silently. users musn't be known about what changes is being made)
Concatenating Field Values Into One Field For An Update And Insert Query
Hello,
I have an ADO based DB viewer and I have a field where I need to concatenate values from 2 other fields in order to get teh right value. Now the concatenating part is easy, however I need to save this in my DB and and that is where the problem is.
I can concatenate the 2 fields and it shows in the textbox (txtFruit = apple, txtVeggie = corn, txt combo = txtFruit & txtVeggie = applecorn), but when I save it or update it, well it never saves. How would I go about doing this, thanks
Code:
"UPDATE Table SET Fruit = '" & DB_Table.txtFruit.Text & "', Veggie = '" & DB_Table.txtVeggie.Text & "', Combo = '" & DB_Table.txtfruit.Text & DB_Table.txtVeggie.Text & "'"
Edited by - jjoseph on 6/4/2007 10:18:40 PM
Editing Access Query Using Vb Code Module Script
I'm developing a little access program thats uses a form to input a date range, which I then need to use to query and export a table. I have the form made and I have the export portion finished. All I need to figure out is whether I can edit an existing query's criteria using vba. If not, is it possible to create a new query that will persist after the code has been run or replace an existing query with one incorporating the new date range. Any help would be greatly appreciated.
Thanks,
Chris
Query Data From Access By Writing A Code In Excel
Here is what I am doing - create a code in Excel to bring data from Access Database in the same folder. Two problems on the following code: 1) when I used double quote for DatePart , the quoted part (like "yyyy") is highlighted as compile error. 2) it also shows the problem in the line of " .Open, , 3, 3 "
I got this code from a friend and just make minor change and expect it to work propertly. It turned out i met these two problems. Please give me a hint how to fix it. Many thanks.
Code:
Sub Import2()
Dim cn As Object, rs As Object, myCallYear As String, myCallMonth As String
Dim MySql As String, dbfullname As String, myCnt As Long
dbfullname = "C:Documents and SettingsxyzDesktopFFR.mdb"
myCallYear = Sheets("Reference").Range("E13").Value 'Pass Year
myCallMonth = Sheets("Reference").Range("E14").Value 'Pass Month
MySql = "SELECT DatePart(''yyyy'',[TransactionTime]),DatePart('m',[TransactionTime]),[Category],[Subcategory],Sum([DollarSpend]),[TransactionTime]" & _
"FROM tblExpense GROUP BY DatePart('yyyy',[TransactionTime]),DatePart('m',[TransactionTime]),Category,Subcategory" & _
"Having DatePart("yyyy",[TransactionTime]) ='" & myCallYear & "'" & "AND DatePart("m",[TransactionTime]) ='" & myCallMonth & "'Order By DatePart("yyyy",[TransactionTime]), DatePart("m",[TransactionTime]);"
myCallYear = Empty
myCallMonth = Empty
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbfullname & ";" 'Create DB connection
Set rs = CreateObject("ADODB.Recordset")
With rs
Set .ActiveConnection = cn
.Source = MySql 'Pass your SQL
.Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
myCnt = .RecordCount
If myCnt > 0 Then
.MoveLast: .MoveFirst
'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
3 fields in the sql pass
Sheets("Data").Range(Cells(2, 14), Cells(myCnt + 1, 21)).CopyFromRecordset rs
End If
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub
(SOLVED)how Make A Query Totally From VB Code For Ms Access Table?
Hi all,please help me to understand this..
I have two tables (table_connection and table_operator)
with following field :
table_connection
1. ID_connection
2. Date_connection
3. Cost
4. ID_oprator
Table operator
1. ID_operator
2. Name
i want to form the query to show on my datareport :
each record is ID_operator, name, sum of cost where Date_connection is between Calendar1.value and calendar2.value (group by ID_operator,group by name,but not group by Date_connection)
if tried to build the query from msaccess side,
but i cannot avoid to have group by date_connection if i use sum for field cost.
i connect to that query this way
Code:
With rsQcost
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open "Query_cost", conAVB, , , adCmdTable
End With
but now i want to do all (including querying) in vb code
idont know how to do that
i only have this code in mind
Code:
Select ID_operator, table_operator.name, sum of cost from table_connectio, table_operator
where Date_connection >= Calendar1.value and Date_connection <= calendar2.value
group by ID_operator
please correct me
thanks
Edited by - kjoephi on 8/8/2004 12:14:33 AM
Access Select Qry - Join Number Field To Text Field
Hey
I have a select query with 2 tables which I want to inner join table1 field1 data type is number with table2 field1 data type text.
I've tried CInt(table2.field1) in SQL but doesn't like this:
SELECT table1.field1, table2.field1,
FROM table1 LEFT JOIN table2 ON table1.field1 = CInt([table2].[field1])
Any ideas folks?
Cheers,
Mudz
Call The Memo Field In Access From Text Field In VB????
Hello
I am running a SQL query in VB to search for anything in the [comments] field defined as Memo in Access database (not text field).
When I run search, I got the error "type mismatch"....
Here is the code:
"WHERE (([DAILY WORK].[Comments]) like ' * " & [txtCommentSearch.Text] & "* ')" & _
(The above code all in one line)
I tried [txtCommentSearch.Memo] but still not working. The txtCommentSearch is the field in VB that I want users to enter any keywords to search.
Can anyone help me, please? Is the wildcard right in VB statement?
I tried to define [txtCommentSearch.text] as variant but still not work. What I done wrong?
Thanks.
Bill
Count Field Incorrect Error/ Access 2K ? In Field Name
I have an Access 2K database with a field named [Shipped?]
When I try to do the following.
rs.open "Select * from [FO#/Main] Where [Shipped?] = False
I get the following error.
[Microsoft][ODBC Microsoft Access Driver] Count field incorecct
I assume it has something to do with the question mark.
I do not want to change the name of this field. That would require a lot of rewriting of code and editing many forms in the Access program.
Is there around this error?
I have not found anything in the MS knowledge base to help me with this.
Someone please help me.
Thanks
Write A VB Field Entry To An Access Table Field
I have a field called lstQuotedQty on my VB form of which I would like the data input by the user into it to go into a field in an Access table called tblStock and the 3rd field in the table called fldSold...
The code seems to run fine, looks at my A: as though searching for the Access Database, but when I look at the Stock table of course nothing new populates the fldSold as I hoped. This is my code: Also note the user may populate the lstQuotedQty with anywhere from 1-to-an infinite number of different items sold.
I only have fldProductNo, fldPrice, fldAmount, fldSold in my tblStock, and fldSold is the only one I want to populate with this data, I'm not sure how large a zip file we are allowed to upload, but will try to attach the whole thing...I am still in the baby stages here, and everything is pointing to the A: if anyone tests this or has opinions to share I'd appreciate it...
Private Sub Add_Item2_Click()
'==================================
Dim adoDSN As ADODB.Connection
Dim strSQL As String
Dim strConString As String
Dim i%
strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& App.Path & "Inventory.mdb" & ";Persist Security Info=False"
Set adoDSN = New ADODB.Connection
adoDSN.Open strConString
For i = 0 To lstQuotedQty.ListCount - 2
strSQL = "Insert Into tblStock (fldSold)"
adoDSN.Execute strSQL
Next i
adoDSN.Close
Set adoDSN = Nothing
End Sub
Populating An Access Table Field With Output From VB Field
I have a field called lstQuotedQty on my VB form of which I would like the data input by the user into it to go into a field in an Access table called tblStock and the 3rd field in the table called fldSold...
The code seems to run fine, looks at my A: as though searching for the Access Database, but when I look at the Stock table of course nothing new populates the fldSold as I hoped. This is my code: Also note the user may populate the lstQuotedQty with anywhere from 1-to-an infinite number of different items sold.
I only have fldProductNo, fldPrice, fldAmount, fldSold in my tblStock, and fldSold is the only one I want to populate with this data, I'm not sure how large a zip file we are allowed to upload, but will try to attach the whole thing...I am still in the baby stages here, and everything is pointing to the A: if anyone tests this or has opinions to share I'd appreciate it...
Private Sub Add_Item2_Click()
'==================================
Dim adoDSN As ADODB.Connection
Dim strSQL As String
Dim strConString As String
Dim i%
strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& App.Path & "Inventory.mdb" & ";Persist Security Info=False"
Set adoDSN = New ADODB.Connection
adoDSN.Open strConString
For i = 0 To lstQuotedQty.ListCount - 2
strSQL = "Insert Into tblStock (fldSold)"
adoDSN.Execute strSQL
Next i
adoDSN.Close
Set adoDSN = Nothing
End Sub
Getting A Certain Field From Query
i would like to get a data from a QUERY and display its "gotten" data to a datagride, but an error always appears when i run the program... it says [ADODC]: unknown error [ado].... here is a sample code...
Code:
If cboCategory.Text = "First Name" Then
SQL = "SELECT LogInLogoutTable.LogDate, Employees.FirstName, Employees.MiddleName, Employees.LastName, Employees.Position, LogInLogoutTable.Late, LogInLogoutTable.Overtime, LogInLogoutTable.Absent, LogInLogoutTable.NumberOfLate, LogInLogoutTable.NumberOfOvertime, LogInLogoutTable.NumberOfAbsent FROM Employees, LogInLogoutTable Where [Employees.FirstName] like '%" + txtSearch.Text + "%'"
End If
AdoEmployeeStatusReport.RecordSource = SQL
AdoEmployeeStatusReport.Refresh
i think there is still an error in my codes somewhere in the bold area, please correct my mistakes....
Query Field Reference
I am wanting to set a defined variable to a field within a query. Here is the following:
********************************
Dim qryLoc1Total As QueryDef
Dim strTotLoc1 As String
Set qryLoc1Total = CurrentDb.QueryDefs _("qry_SalesTotalCurMonth_TotalsLoc1")
*********************************
How do I make strTotloc1 variable equal a field called TotalSales within qryLoc1Total?
Thanks
Query Field On/Off Control?
In the Query design mode there is row titled "Show" with check boxes for each Query field.
Is there any way to check/uncheck that box through VB code?
One Field Is Missing From Query, WHY??
Here is my SQL statement to display all the query in the mshflexgrid:
VB Code:
SELECT Cutt_Master.Month, Cutt_Master.Prod_Code, Cutt_Master.Part_Code,Cutt_Master.Cutt_Date,((Cutt_Master.Qty/1000)*Cost_Price.Origin_Cost) AS Amount, Cost_Price.Origin_Cost,Cost_Price.Insp_Cost, Cost_Price.InProcess_Cost, Cost_Price.Sales_Price " & _ "FROM Cutt_Master INNER JOIN Cost_Price ON Cutt_Master.Part_Code = Cost_Price.Part_Code " & _ "WHERE Cutt_Date BETWEEN #" & dtpFrom.Value & "# AND #" & dtpUntil.Value & "#
but only cutt_date is missing in the mshflexgrid...
i dont know why...
anyone knows??
Memo Field In Query
Guys
I have something a tad strange happening but I'm sure one of you clever chaps will be able to figure it out. Ok, here goes.
This query:
VB Code:
SELECT tbljobdetails.jobno, tbljobdetails.clientID, tbljobdetails.cpc1, tbljobdetails.cpc2,tbljobdetails.cpc3, tbljobdetails.cpc4, tbljobdetails.prodname1, tbljobdetails.prodname2,tbljobdetails.prodname3, tbljobdetails.prodname4, tbljobdetails.plasno1, tbljobdetails.plasno2,tbljobdetails.plasno3, tbljobdetails.plasno4, tbljobdetails.batchesno1, tbljobdetails.batchesno2,tbljobdetails.batchesno3, tbljobdetails.batchesno4, '5137' AS clientid, 'blah blah'AS customer, 'L:mydatalahlah.dat' AS Infile,[b]tblerrortext.errortext AS Errortext[/b],Sum(([tbljobdetails].[plasno1]+[tbljobdetails].[plasno2]+[tbljobdetails].[plasno3]+[tbljobdetails].[plasno4]))AS sumPlas, sum(([tbljobdetails].[batchesno1]+[tbljobdetails].[batchesno2]+[tbljobdetails].[batchesno3]+[tbljobdetails].[batchesno4]))as sumBatches FROM tbljobdetails LEFT JOIN tblerrortext ON tbljobdetails.jobno = tblerrortext.JobNoGROUP BY tbljobdetails.jobno, tbljobdetails.clientID, tbljobdetails.cpc1, tbljobdetails.cpc2, tbljobdetails.cpc3,tbljobdetails.cpc4, tbljobdetails.prodname1, tbljobdetails.prodname2, tbljobdetails.prodname3, tbljobdetails.prodname4,tbljobdetails.plasno1, tbljobdetails.plasno2, tbljobdetails.plasno3, tbljobdetails.plasno4, tbljobdetails.batchesno1,tbljobdetails.batchesno2, tbljobdetails.batchesno3, tbljobdetails.batchesno4, '1234','blah blah','L:lah1234567lah.dat', tblerrortext.errortext, tbljobdetails.id HAVING (((tbljobdetails.id)=102));"
Gives me the information that I need but the tblerrortext.errortext returns a '?' in the query but the actual value in the tblerrortext table is 'Here is an error' or something.
Any ideas!?
Cheers guys
Query Field Error
I am using
myquery = "select * from act where Phone A = '2144331'"
to query my data base. The field name is 'Phone A'. It is a Text field. It seams that my field name is not exepted because it is made out of two words Phone & A. How can I fix this problem without renaming the field.
Thanks
Sanitago
Query An Encrypted Field
I inherited this system that encrypts data in a field that I need to query (In Access now) yet I cannot figure out how to query this field based upon the encryptions. For Example I need to query this where this field = "1". IN the DB this is how the field is encrypted from Table DCRLogFast on Field Books:
IINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNINNNNINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
'Now in the code in VB this is how the table is queried and 'it works! It replaces the position of the field with an I for the number entered. The 'temp' variable is the user input, whereas here I would have entered "1".
Books = "??????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????? ?"
Mid$(Books, temp, 1) = "I"
SQL = "Select * from DcrLogFast where Books like '" & Books & "'"
Anybody got any query suggestions for Access.
VB6 Query On A Date Field
I need assistance concatenating the # character to a variable that contains a date in an sql statement.
The portion of the statement in qestions should look something like this.
#variablecontaingdate#
Any help woulod be appreciated.
Query XML Data From DB Field
Hi All,
I have an XML Doc loaded into a SQL Server DB field of type 'text'. I want to query the XML info, here is the SQL Server code I have so far:
Code:
DECLARE @DOC varchar (8000)
DECLARE @idoc int
select @doc = xmldata from xmltest
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/NeededInfo')
WITH (NeededInfo ntext '@mp:xmltext')
This is OK but the XML field is of type text beacuse it's HUGE and I can't declare @DOC as type text. So, How do I get the entire XML info into the 'sp_xml_preparedocument @idoc OUTPUT, @doc' stored proc?
THX
Cyphin
~Of all the things I've lost, I miss my mind the most.~
~Iz vseh veshei chto ya poteryal, ya bol'she vsego skuchayu po rassudku.~
Edited by - Cyphin78 on 7/7/2005 1:40:10 PM
How Define An Excel Field In A SQL Query
Hi,
I need to realize a conditional insert from Excel to SQL.
The code is the following:
INSERT INTO MKT_TST.dbo.TEMP
(conto,importo,versione,anno,mese,rettifica)
SELECT * FROM OPENQUERY(BASE_DATI,'SELECT * FROM [BASE_DATI$]')
WHERE TEMP.versione <> (SELECT * FROM OPENQUERY(BASE_DATI,'SELECT * FROM [BASE_DATI$.Versione]'))
I have an error related to field VERSIONE;
the message error is:
OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'BASE_DATI$.Versione'
What's the correct way to call the Excel field?
Thank u!
Place Query In Text Field
I have a query which counts Rows;
SELECT tblEquipment.Location_ID, count (tblEquipment.Equipment_ID) as Total_Equipment
FROM tblEquipment
GROUP BY tblEquipment.Location_ID;
I also have a text field named txtNumber. what I want is for the number in the Total_Equipment column from the query to go into my text field? ANyone know how to do this?
Another problem may be that I already have a query for the recordsource. Anyone know how to put two queries in the load event?
Select Query Only Returning One Field
I'm using the following code to load account data into a flex grid table:
Code:
Public Sub LoadAccounts(ByVal myGrid As MSFlexGrid)
modAccount.DBConn_Open
Dim x As Variant 'represents the field in the RecordSet
Dim i As Integer 'represents the index of the field (i.e. x)
Dim LastRow As Integer
i = 1
RS.Open "SELECT AccountName FROM Account", Conn
For Each x In RS.Fields
Debug.Print RS.Fields.Count
myGrid.Rows = myGrid.Rows + 1
LastRow = myGrid.Rows
myGrid.TextMatrix(i, 0) = RS.Fields("AccountName").Value
i = i + 1
Next x
DBConn_Close
End Sub
However, the 'Debug.Print RS.Fields.Count' only returns 1 (there are four test accounts) and the flex grid only displays one name - the same everytime. What's going on?
|
TRACKER
