Code:
Function Write_rstADO_to_CurrdB_Table()
'Assumes you have already setup a DSN to your Server
'Assumes YOURDESTINATIONTABLE is the same structure as your SERVER.TABLE
Dim cnnADO As ADODB.Connection
Dim wkspDAO As DAO.Workspace
[Code] ....
I`m currently having the problem to export data from an SQL server into a table. I managed to open a recordset but I`m incapable of adding the recordset to an existing table. I found similar threads but I am still not able to generate functioning code.
Code:
Function fDAOServerRecordset()
Dim db As DAO.Database
Dim dblcl As DAO.Database
Dim rssql As DAO.Recordset
[code]....
I have a DAO.recordset called "rsSQLIn". This comes from a csv file by:
Code:
strSql = "SELECT * " _
& "FROM [Text;Database=" _
& strFolder _
[Code]....
While the validation runs a boolean keeps track of validated input and errored input.
After validation the validated input is dumped in the table.
Now what I want is de saving the errored record from "rsSQLIn" to be copied to a new .csv file.
The problem I have is that I cant seem to get the current record from the recordset "rsSQLIn". How do I reference this? I need the complete set of 24 fields being the same within "rsSQLIn"
Is there a way to print the current record from a dao.recordset?
This is an exercise to compare data content.
I have a table with 30 fields and thousands of rows (rs1).
I'm comparing it with a copy of the same table (rs2) that has the same number of fields and the same rows and almost the same content.
I can loop through the recordsets and get the cursor to stop on a field whose values don't match, lets say on row #x
and the programmed message will say something like:
"ROW: 699 Field: [RequestStatus] rs1.VALUE: Closed, DOES NOT MATCH rs2.Value: VOID, in the comparison recordset"Then I'd like to print the entire Row, Row #699.
I thought I could use rs1.getrows but I'm not sure how to make that work.
Hi all,
I have a form with around 10 checkboxes which serve as a filter option...now, when I hit my cmdFilter button it works well with a simple MsgBox !Ime showing all the filtered names...now, I want to put the results into a table tblTemp so that I could show the results in my subform. I've tried with making a sql string something like "INSERT INTO tblTemp..." but it's still empty.:confused:
Since this table will serve as a one time data, I will need to delete all records when I hit the Filter button next time...so, how do I send my recordset data into my table.
I hope this sounds understandable...
Thanks a lot,
Daniel
I am trying to formulate some code to run an SQL statement but it is not working.
The statement runs from a button on the sub form subOriginForm. Its intention is to collect the values from cboField1 and txtfield2 located on that form and insert them into tblTargetDB - but the whole thing isn't working.
Code:
Private Sub cmdMyButton_Click()
'DoCmd.SetWarnings False
INSERT INTO tblTargetDB ( field1, field2)
VALUES (forms!subOriginForm.cboField1, forms!subOriginform.txtfield2);
DoCmd.SetWarnings True
End Sub
I was wondering if it was possible to link random external tables to the Active Database through VBA. I would like to run the code that would open up a dialog box that would let the user select the database as well as the tables within that database that the user can select to link to. I am able to select the database and but not able to select the actual tables. The tables will be random so I can't make a constant statement for a specific database.
View 1 Replies View RelatedWhat I'd like to do is have an "export button", where the OnClick event, exports a single table into a new accde file. This would allow the end users to zip this file up and send it to me.
Because of all sorts of stupidity, I have no network capability and must send data back and forth via email.
I have digressed to a replication table that needs to be uploaded, once data is entered by the users...
Because the files can get relatively large (for emailing purposes), I am trying to figure out how I can get just one table from them vs. sending the whole application file back to me... It's pretty vital that they not be able to edit the table because that could really mess up the process.
So any way to create a new lock file from within the current Db?
I have inherited an old database with a new field to add some new invoice numbers and increment the number to each recordset by + 1. Starting at 001. I have formatted the field to a Number Field and formatted it to 000"/2015". I now need to populate over 2000 records with the sequential numbers for each record.
I have created the following code as my attempt. Of course it doesn't work!
Code:
Sub AddNumbertoRecords()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim counter As Long
counter = 1
Set db = CurrentDb()
[Code] ....
I have a normal Access table. The first column is ID and is the primary key. The second column is a text. The table consists of two records and is sorted in ascending order. First comes the data set with the ID 1.
Sub test()
Set rs_access = CurrentDb.OpenRecordset("tab1")
rs_access.MoveFirst
MsgBox (rs_access.Fields("id").Value)
End Sub
The message box shows 1. So everything is fine. Now I sort the table descending. But I get the same message. Why? Appearently the recordset doesn't know that the table was modified. The recordset should register that the table is modified.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229
I'm having difficulties creating a sql statement which updates data in another Access database from a current database.
I'm able to do an insert and delete statement just perfectly, but not an Update. :confused:
For example, here is a dummy sample (which does not work):
Update tbl_test1 IN '\C:Tempabc.mdb', tbl_test2
set tbl_test1.Name = tbl_test2.Name
Where tbl_test1.ID = tbl_test2.ID
I tried a couple of variations, however, I keep getting an error.
Also, I don't want to create a database link (due to the sheer complexity of my project, which I'll spare you the details..)
Does anyone know how to do this? I searched the forums and came up with zilch! =(.. Help??!
i'm getting this message when i try and change a record in a table via a recordset... but i am using CursorType = adLockOptimistic which i thought let you make changes to the table
here is my code so far
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.CursorType = adLockOptimistic
rst.Open "TBL_TmpSubmission", CurrentProject.Connection
If rst.RecordCount > 0 Then
Do While Not rst.EOF
MsgBox rst!PropertyType, vbOKOnly, "debug"
If DCount("[PropertyType]", "[TBL_PropertyType]", "PropertyType = '" & rst!PropertyType & "'") <> 1 Then
rst!PropertyType = DLookup("[PropertyType]", "[TBL_PropertyType]", "IDPropertyType = " & rst!PropertyType)
MsgBox "property changed", vbOKOnly, "debug"
Else
MsgBox "good property", vbOKOnly, "debug"
End If
rst.MoveNext
Loop
End If
rst.Close
am i using the wrong combination or cursor and lock type here? reading the help it seems i should be able to make changes to the table.
I am trying to use a Connection to an xlsx spredsheet to read in a table of information (the data is not a table, just laid out in a table, see attached, the one I am trying to load is xlsx not xls but I cannot for some reson upload the xlsx one on here)
I have the following declared:
Code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
my connection string is:
Code:
strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & cstrFolder &
Chr(92) & tempSymbol & ".xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';"
I open the connection which works fine:
Code : cn.Open strConnectionString
Now here I hit the problem, I have the following SQL string:
Code : strSelect = "SELECT * from [table$]"
The spreadsheet contains just 1 tab, named table so I think I am referring to the data correctly in my SQL string?
When I open the recordet for variable rs using:
Code : rs.Open strSelect, cn, adOpenStatic, adLockOptimistic, adCmdText
I get the error dialogue box:
The Microsoft Access database engine could not find the object 'table$'. make sure the object exists and that you spell its name and the path name correctly.....
I did a Debug.print to check the strConnectionString was correct and the Source is exactly correct and refers to the name of the file I am trying to access exactly.
I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.
Code:
Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
emplid = rs![Employee Number]
[Code] ....
This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.
I firstly created a query that selected the nessary fields I wish to update from the two tables.
And its apparent that I need to use RecordSet to insert the information from the form into the query.
I have found a few different ways of doing this - none of which work for me :( HELP!
Method 1
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim rs As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
Me.txtserver = rs!Server
Me.cmblocation = rs!Location
Me.cmbtype = rs!BackupType
Me.cmbtype = rs!Type
Me.cmbsize = rs!Size(GB)
Me.cmbassign = rs!Group
Me.txtrequiredby = rs!Date_required_by
Me.txtrequestedby = rs!Requested_by
Me.txtrequest = rs!Date_Time_Request
Me.txtnotes = rs!Notes
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
rs.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
[B]Method 2
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim sqlStatement As String
Dim saverecord As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
sqlStatement = "SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;"
Set db = CurrentDb()
Set saverecord = db.OpenRecordset(sqlStatement)
saverecord.AddNew
saverecord(0) = txtserver
saverecord(1) = cmblocation
saverecord(2) = cmbtype
saverecord(3) = cmbtype
saverecord(4) = cmbsize
saverecord(5) = cmbassign
saverecord(6) = txtrequiredby
saverecord(7) = txtrequestedby
saverecord(8) = txtrequest
saverecord(9) = txtnotes
saverecord.Update 'Write new record to database
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
saverecord.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
and I have even looked into an insert sql statement
[B]Method 3
Dim SQL_Text As String
SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &"
INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;"
Docmd.RunSQL (SQL_Text, false)
Method 1 seems to be popular but its returning the message
Run time error '3061'
Too few parameters. Expected 3.
:confused:
I am reading through a table looking for duplicate values in the FullName text field. I want to store in a new table the duplicate records I find, storing just the MemberNumber and the FullName. When the VBA code runs and finds duplicates, the SQL statement to insert a new record into the Duplicates table asks for the value of LastMemberNumber and LastFullName when it already has the values and has displayed them in the message boxes! What am I doing wrong?
The code is:
Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim dbs As Database
Dim LastMemberNumber As Integer
Dim LastFullName As String
[code]....
How to Get latest id and inserted to another table like this image below
sdrv.ms/18HQ
See..i want in dbo_maid get id and insert to dbo_contract in maid_id
I have a form that is calling data from a table. Then the user can determine if they approve the question or not. So what I am trying to do is take one combo box that has yes or no and write it to the database. I thought I had it correct, but appears I do not as it is not writing yes or no to the table. The field initially has no value and that is my query for calling the data for the user to select from. Here is the code that I have so far
<code>
'add approval to yes
CurrentDb.Execute "INSERT INTO travelerQA(approval)" & _
" VALUES('" & Me.cmbapproveda & "')"
</code>
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
Code:
<%
function combomaker(elSQL, elFieldNameID, elFieldName, elSelected, elConn)
' This function creates a generic combo box with values from a table
dim elRS
set elRS = server.CreateObject("ADODB.Recordset")
set elRS = elConn.Execute(elSQL)
elRS.MoveFirst
do while not elRS.eof
response.Write "<option value='" & elRS.fields(elFieldNameID) & "'"
if elSelected <> "" then
if cstr(elRS.Fields(elFieldNameID)) = elSelected then
response.Write " selected "
end if
else
if elRS.BOF then
response.Write " selected "
end if
end if
response.Write ">" & elRS.fields(elFieldName) & "</option>" & vbCrLf
elRS.MoveNext
loop
elRS.close
set elRS = nothing
end function
%>
My form respectively subform has a couple of problems related to the recordset as it is available in VBA.
The mainform contains material data, the subform contains the components of that material and a quantity, while the components are materials themselves. The subform's control source is an SQL statement created by the query builder.
Everything worked fine before i replaced the material-selecting combobox in the continuous subform by a textbox and a button. That button leads to another (dialog) form with some filtering options, which in turn returns the number of the selected material. This material gets inserted into the textbox. To this point it works fine.
But when i enter a quantity before i selected a Material, i get an error message after selecting the Material: This Action was Cancelled by an Associated Object. This happens while executing the following VBA Code on click of the material selection button (exact position commented in code):
Code:
Private Sub cbuSelectComponent_Click()
' Select component
Dim SQL As String
Dim rs As Recordset
DoCmd.OpenForm "Material Selector Dialog", , , , , acDialog, "Dialog"
If GLB_selected_mat = -1 Then 'cancel
[Code] ....
I've found the following Microsoft KB Article: [URL] ..... In their example code they use:
' Restore text box value to the original record contents
' in this case, that is NULL
datDataCtl.UpdateControls
I assume this is the relevant part, but i have no DataControl (what's that?) and neither found an UpdateControls method in the subform object.
The second error, "no current recordset", occures when i edit an existing component line in the subform that has been added right before (also using the same event and code as mentioned above). If i close the form after adding the component and open it again, it's no problem.
I'm trying to populate a listbox from a recordset. I will explain what I'm trying to achieve.
I have a (continuous) form with some data from a table. On the form header I have a listbox, showing all (distinct) customers from that form.
The user is able to do some filtering. That all works great. But I want to populate the listbox with ONLY the customers on the form AFTER filtering.
One way I thought might me the solution was using RecordClone. But I'm stuck there. It must be something simple for an expert. I'm not entirely a newbie, at least not to VBA. Is there an easy way to refer the listbox' recordset to the actual recordset on the form?
I am adding new record into subform via recordsetclone method. The problem is that record is added but on save it does not appear in the table. If add this record manual using subform everything works. When record added manually update of the record works fine.
C
'Add Wastage value to flooring area section
Private Sub Wastage_AfterUpdate()
Dim rsFlArea As DAO.Recordset
Dim Wastage As Double
Dim Item As String
Set rsFlArea = Me.OrderFloorAreaEdit.Form.RecordsetClone
[Code] .....
Help!
Im trying to get a text box on a form to always automatically insert the current date. This seems like it would be so easy to me but apparently im stooopid.
Thanks to anyone that can help.
( I already have the field in the table, and its in the query)
Help!
Im trying to get a text box on a form to always automatically insert the current date. This seems like it would be so easy to me but apparently im stooopid.
Thanks to anyone that can help.
( I already have the field in the table, and its in the query)