Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Retrieve Data From MSAccess Table Using VBA


I am trying to open a Table using VBA while already in the Database in MSAccess. I started doing this but there has to be a much easier way.
Sub TestOpen()

Dim dbMain As New ADODB.Connection
Dim rsOrder As New ADODB.Recordset
Dim SQL As String

dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=C:ESSFOrder.mdb"
SQL = "Select * from WillOrder"'Table named WillOrder
rsOrder.Open SQL, dbMain, adOpenDynamic, adLockOptimistic

Do While Not rsCustomer.EOF
Debug.Print "The Order Number is: " & rsOrder("OrderNumber")
Debug.Print "The ItemKey is: " & rsOrder("Itemky")
rsOrder.Update
rsOrder.MoveNext
Loop
rsOrder.Close
dbMain.Close

End SubAny help would be appreciated. Thanks.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Inserting Data From Array Into MSAccess Table
Hello, I would like someone to look at my code and give me some suggestions on how I could make it more efficient.

I am developing a MSAccessXP application that uses data on a SQL server. Since this is for use a laptop that will sometimes be disconnected from the company network, I need to fill tables in the Access database with data from my SQl database. I've tried several methods to do this, the one I am working with now works good but I think it's a little slow inserting the data into the Access table.

Here is the procedure for retrieving data from SQL server (This is in a class module):

Code:
Public Function GetCustomerArray() As String()
On Error GoTo err1
' Declarations
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim i As Long
Dim count As Long
Dim strCustomer() As String
OpenConnection 'Call custom sub to open connection to SQL Server
rs.CursorLocation = adUseClient 'Prepare and fill recordset
rs.Open "SELECT * FROM uvwCustomer", cn, adOpenKeyset, adLockOptimistic
count = rs.RecordCount
ReDim strCustomer(count) 'assign upper bound to array
For i = 1 To count 'loop through recordset to fill array
strCustomer(i) = rs(0)
rs.MoveNext
Next i
GetCustomerArray = strCustomer 'return array
ex1: 'Exit routine
CloseConnection
If Not rs Is Nothing Then
Set rs = Nothing
End If
Exit Function
err1: 'Error handling
MsgBox Err.Description & ", " & Err.Number
Resume ex1
End Function
This procedure is pretty quick. I've found in my tests that returning an array instead of a recordset seems to be quicker in the calling procedure.

Here is the procedure for filling the Access table with values from the array:

Code:
Public Sub FillCustomer()
On Error GoTo err1
'Declarations
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim i As Long
Dim strCustomer() As String
Dim oData As clGetData
Set oData = New clGetData
With cmd 'Clear tblCustomer
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "DELETE * FROM tblCustomer"
.Execute
End With
strCustomer = oData.GetCustomerArray 'Assign value of method to array
[b]For i = 1 To UBound(strCustomer) 'loop through array and insert values into
With cmd 'MSAccess table.
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "INSERT INTO tblCustomer (CustomerName) " & _
"VALUES (?)"
.Execute Parameters:=strCustomer(i)
End With
Next i[/b]'Exit routine
ex1:
If Not cmd Is Nothing Then
Set cmd = Nothing
End If
If Not oData Is Nothing Then
Set oData = Nothing
End If
Exit Sub
'Error handling
err1:
MsgBox Err.Description & ", " & Err.Number
Resume ex1
End Sub

The highlighted block take about 5 to 6 seconds to execute. That may not seem like much but for this test I'm using a small table (222 records) with one column. When I start using larger queries with more records and multiple columns this going to get slow. I would like to know if there is way to assign an array or recordset to a Access table like you can with the recordset properties of forms and reports.

Anyway please take a look and give me some suggestions on how I might do this better.

Thank you

Warren

Insert Data Into A New Table MSACCESS -RESOLVED
I have a new table that i created

I want to be able to use the results from the select statement below to be inserted into the new table once I click a button.

LSQL = "select * from paymentDPA"
 LSQL = LSQL & " where tklast LIKE '*" & LSearchString & "*' And lmatter LIKE '*" & LSearchString1 & "*'And (ltradat Between #" & LSearchString2 & "# And #" & LSearchString3 & "#)"


then delete the data from the table once I export it into Eexcel

Can someone please help??

Thank You





Edited by - dtttj on 8/31/2006 2:17:36 PM

How To Retrieve Ms Excell Data (sheet Name) To Sql Table Using Vb (Data Conversion)
hi
i have problem to retrieve a ms excell data to sql table (i'am using sql 2000) using visual basic.
then another problem is after the conversion process, how to retrieve the certain sql field (example name field) and
make it into shortname (example robert joseph to robert) without adding the last name and update it into sql table.
my limitation for the shortname is 12 character.
plss help me

How To Retrieve Data (More Than 1 Table)
Hi all I have one question here about the sql. How to retrieve a data if there is more than one table. What I have done so far, if more than one table is involved, I make a query in Access DB. Then I just retrieved that query in VB. Can someone tell me the ways?

Let say I want to update 2 tables. tblRequest and tblItem. How do I retrieved data from tblItem.


VB Code:
Private Sub cmdUpdate_Click() Dim sql As StringSet rs = New ADODB.Recordsetsql = "Select * from tblRequest where ReqNo='" & txtReqNo.Text & "'"rs.Open sql, Cnn, adOpenKeyset, adLockPessimistic With rs If .RecordCount > 0 Then!ReqBy = UCase(txtReqBy.Text)!desc1 = txtDesc1.Text If MsgBox("Are you sure to Update?", vbOKCancel, "Update Data") = vbOK Then.UpdateMsgBox "Data is Successfully Updated", vbOKOnly + vbInformation, "INFORMATION"End If End IfEnd Withrs.CloseSet rs = NothingEnd Sub
Thanks in advance

Retrieve Data From Sql Table Into A Listbox
Hi everyone,

I have a enter data into a listbox i.e filenames and save them into a sql server database table, but I am having problems in retrieving the information back from the table into the listbox when I select a specific record to be retrieved. Can anyone help please.
Below is the stored procedure and the VB code I am working with. This is a learning process for me.

Many thanks.

dfuas

CREATE PROCEDURE SP_CREQUEST_GETATTACHMENTS (@RequestID int)

AS

SELECT A.Attachment

FROM Attachments A INNER JOIN Request R
On A.RequestID = R.RequestID
--INNER JOIN Notes N ON R.RequestID = N.RequestID

WHEREA.RequestID = @RequestID
GO



VB Code:
Private Function GetAttachments(RequestId As Integer)      Dim cmd As ADODB.Command    Dim rst As ADODB.Recordset     Set cmd = New ADODB.Command    Set rst = New ADODB.Recordset        OpenConn        With cmd        .ActiveConnection = cn        .CommandType = adCmdStoredProc        .CommandText = "SP_CREQUEST_GETATTACHMENTS"        .Parameters.Append .CreateParameter("RequestID", adInteger, adParamInput, , RequestId)                    Set rst.DataSource = .Execute                Me.lstAddiotionalInfo.AddItem rst.Fields.Item.Value            End With        CloseConn

Retrieve Data From An Access Table Using SQL
Hi,

I am making a program which takes a list of publishers from Access and inserts all the records into a ComboList for selection. (Done this bit). Once a publisher is selected from the drop down list I need the next ComboList to display all of the magazines/books made by them. At the moment I have the magazines stored in tables in Access under each publisher. The problem being is, that over time more and more publishers will be added to the list so I can't hard code the SELECT * FROM tablename ORDER BY fieldname, etc.

Is there a statement which I can use that will be like something below?


SELECT * FROM cmbPublisher.text ORDER BY Magazine

Sorry if this doesn't make sense, if not, I will re-write it when requested.

Hope someone can help.

Regards
Tosh

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

How To Retrieve The Picture That Was Saved In MSAccess
Please help. I have created an AccessDB File ver.2000, then create a table "tblPic", then Create a Field "fldPic" and its datatype is OLE Object. I will use that table TO SAVE THE ACTUAL PICTURES (the JPGs, not the path of the pictures). How will i retrieve that pictures in my VBProgram? What control will i use and how will i do it? Thanks in advance.

How To Retrieve The Latest Saved Autonumber (primary Key) Using Vb6 - Ado - Msaccess
Help please!!!

Id like to know how can I get the my latest saved autonumber (primary key) using vb6 - ado - msaccess.

Suppose I have one record added in a table, I'd like to get the saved autonumber so that I can reference it to another table.

help, please!!!

ADO Data Control Did Not Retrieve Newly Added AutoNumber/Identity Fields: It Did Retrieve 0 Only.
I USE:
ADO 2.5
LOCAL DATABASE ACCESS 97
Clientside cursors
Microsoft OLE DB Provider for Jet, version 4.0
BUT
the Microsoft DataGrid Control 6.0 bound to an ADO Data Control did not retrieve newly added AutoNumber/Identity fields: it did retrieve 0 only.

How can I decide this problem?




Sukhodub D. S.

When Retrieve Data From DB Whole Screen Is Jam, How To Cancel Data Retrieve..
When retrieve data from DB whole screen is jam, how to cancel data retrieve..

I want to create a function to end the data retrieve function... but when click to retrieve data whole screen already like hang... any button also cannot click before the task is complete.....

Updating Via VB6 Into MSAccess Table
Hallo aiders...

I have a small prob...
My access Table has hundreds of records

Now i hv inserted another field in the table S.No.
I wanted to put the serial numbers through VB6 Coding

This is what i have done...

dim printmont
Dim rsSC as new adodb.recordset
Printmont = month(date)-1
rsSC.CursorLocation = adUseClient
rsSC.Open "Select * from Surcharge where surcharge.mtest = " & printmont & " order by Tripdate", edb, adOpenDynamic, adLockOptimistic

rsSC.MoveFirst
For i = 1 to rsSC.recordcount
rsSC.field(9).value = format(i,"#000")
rsSC.movenext
Next

Please provide some better solution on this issue
Thank you

Deleting Table In MsAccess
hai everyone, i am using vb 6 and ms access 2003. i have 3 tables in access named Table1,Table2,Table3 and Table1 contains stud_id as the primary key and Table2,Table3 has the reference of it. I want to delete Table1 from vb, i used the following code

Dim stud_id As String
stud_id = InputBox("Enter the Student ID")
rs.Open "select * from Table1 where Stud_id = '" & stud_id & "'", cn, adOpenStatic, adLockOptimistic

but its not deleting that row. it says "The record cannot be deleted or updated because table 'Table2' contains related records."

But if i use the same code to delete table2 it works fine. So how i can deleted the item in table1 without opening Table2 and Table3.

I attach my database and vb project for Your reference.

Table1:
Stud_id,name,DOB
Table2:
VMAKE,VACT,VSPARK
Table3:
Centre_id,Centrename

Thank you.

MSAccess Table Comparison
I would like to compare two tables to see if they contain the same distinct values for one field. If they do, I want the sub to exit out, otherwise continue with the process. This is how I am thinking of doing it but wanted to make sure it make sense before I start figuring out the code.

1. Run a SELECT DISTINCT on the existing table and store values in an array.
2. Run a SELECT DISTINCT on the loaded table and store values in an array.
3. Compare two arrays:


Code:
If array1 = array2 then
msgbox "The tables have the same values"
else
'continue code
End if

Does this look like a good way to do things?

Thanks for the assistance!

Conversion PDF To MsAccess Table
Hi ,
I am using VB6 - MsAccess.
My Appl. needs data from PDF file (PDF shows inf. in columnar format)
How to get data from PDF & store in Access table, so that appl. can
use it for further process?
Conversion has to be done externally (to the appl.) only or is it possible within appl. ?
don't have any idea? Please,advise!

Thanks,
Rachana

Autonumber In MSAccess Table
But one doubt. In a Multiuser/Shared environment
compacting is possible ?


[Edited by PC Babu on 12-20-2000 at 12:18 PM]

Adding Unformated Table To The Msaccess
in ms access if i need to add to the table another table (.log) that is stored in text only format with only spaces separating the columns what do i do?
What i need is to automate the process of importing it and adding it to anothe table. I can read it from the .log file just fine, but i need help with adding all those variables to the end of another table.

Creating MSAccess Linked Table
I don't have the english version of access so I don't know how the command is called exactly. But if you create a New Database file from inside access, instead of creating a new table, you may create a link to a table in another database.
This is what I want to do from VB6. It is really necessary that this be created from VB.

I have been looking in this threads FAQ and found a code to create database files from VB. I've got only the "CreateDatabase" Function and improved it.
I'm posting a little app I've created using this module.

What I want to know is how to create a link to a table in another database? More specifically, what ADOX object do I have to use and which methods and/or properties of it?

P.S.: for this module to work you have to add in the Project's References: Microsoft ADO Ext. 2.7 for DDL and Security

Copying Recordset Into An MSACCESS Table (VBA)
I have a recordset of data that I pulled from excel (rstFromTable), what is the best way to dump that data into an MSACCESS table (rstToTable)? (using VBA in the recipient MSACCESS database)

Assume that there are 50 fields per record.

I will show the way that I have been using for a couple years now.


Code:
Do Until rstFromTable.EOF
lColCount = 0
rstToTable.AddNew
For Each fldTemp In rstFromTable.Fields
rstToTable.Fields(lColCount).Value = fldTemp.Value
lColCount = lColCount + 1
Next fldTemp
rstFromTable.MoveNext
Loop

Would it be quicker to write a loop to attach the field names to an SQL string and use an INSERT?

How I Can Insert Two Images In A MsAccess Table?
i have got a form with two textbox with the address of my images, and i want when i click on the ok button insert those images in my MsAccess Table.

thx

Msaccess Table Field Properties In Vb
How can I use the MSACCESS Table Field properties in VB. I need to write a program that displays the properties of fields of a table in Visual basic.

How To Populate The Listview With MSAccess Table?
hi,

i have a listview which i want to populate with one MSAccess table named client_details, i have written the program and ran it, there is no error only problem is that the table is not visible in that listview, plz check if my code is correct or not. The code is given below


VB Code:
Option ExplicitDim mclient_profilequery As StringDim mdate As StringDim mSerialno As StringDim mContactperson As StringDim mCompany As StringDim k As IntegerDim ki As Integer  Private Sub cmdopen_Click()Select case "Company"Case '"clientprofile"mShowPrevious = TruefrmClientProfile.Show End Sub Private Sub Form_Load()connect_detailsPopulateDataEnd Sub Public Sub PopulateData()lvwclientprofiles.ListItems.Clearmclient_profilequery = "SELECT * FROM client_profile "If rs1.State = 1 Then rs1.Closers1.Open mclient_profilequery, con, adOpenStatic, adLockReadOnly If rs1.EOF = True Or rs1.BOF = False Then Exit Subrs1.MoveFirstk = 1ki = ""Do While Not rs1.EOF = Trueki = "clientprofile" & k          lvwclientprofiles.ListItems.Add k, ki, rs1.Fields(0).Value        lvwclientprofiles.ListItems(1).SubItems(1) = rs1.Fields(1).Value        lvwclientprofiles.ListItems(2).SubItems(2) = rs1.Fields(2).Value        lvwclientprofiles.ListItems(3).SubItems(3) = rs1.Fields(3).Valuek = k + 1rs1.MoveNextLoopEnd Sub  Private Sub lvwclientprofiles_ItemClick(ByVal Item As MSComctlLib.ListItem)mdate = mdate.TextSerialno = Item.SubItems(1)Company = Item.SubItems(2)Contactperson = Item.SubItems(3)  End Sub






Edit: Added vbcode end tag and corrected vbcode begin tag - Hack

Get Table Names From MSAccess Datasource
Hello all,

I have a vb6 (not .NET) program using MS Access as the backend. As part of
an import form, I need to allow the user to select the table containing the
data to be imported. How can I populate a combo with this information?
Then, once this value is selected, how can I populate other combos with the
column names of the selected table?


Thanks,

Steven Smith

Load MSAccess Table Onto Form?Possible?
Hi there,
is there a way to load a MS Access table onto a vb form so that records/fields can be edited and are save automatically to the db, ie there is a direct live link to the db. Is there a component available that does this?
Thanks in advance
Williery

Show The Field Caption Of Msaccess Table
Hi ,
How I can get the captions of fields in the ms access table in VB6?Like
field("Field1").caption in ADO
Thanks

Copy OLE Object From MSAccess Table To Clipboard.
I'm trying to copy an OLE object in an MSAccess table that is storing BMP's. I am having trouble declaring the object as the correct data type and and copying it to a clipboard in VB code. How do you copy the text 'Bitmap Image' from an OLE Object field in a table using VB code? Any help would be appreciated. Here's some code that show's what I'm trying to do:

Private Sub main()

Dim dbsNorthwind As Database
Dim rstSymbs As Recordset

Set dbsNorthwind = OpenDatabase("C:jogairspecdbaero.mde")

' Default is dbOpenTable.
Set rstSymbs = dbsNorthwind.OpenRecordset("tblSymbol_Set")


MsgBox rstSymbs.Fields(1).Name

Dim OLE1 As Object
rstSymbs.MoveFirst
Set OLE1 = rstSymbs![MCGImage]
Clipboard.Clear
MsgBox OLE1
OLE1.Copy
End Sub

Show The Field Caption Of Msaccess Table
Edit: Re: http://www.xtremevbtalk.com/showthread.php?t=260267

I'm having the same problem:

Code:
Set rs = DBEngine(0)(0).OpenRecordset("SELECT TOP 1 " & Join(SubcomponentHeaders, ",") & " FROM Subcomponents")
On Error Resume Next
strTemp = ""
For i = 0 To rs.Fields.Count - 1
strTemp = strTemp & rs.Fields(i).Properties("Caption").Value
If Err.Number <> 0 Then
strTemp = strTemp & rs.Fields(i).Name & ","
Else
strTemp = strTemp & ","
End If
Next
On Error GoTo 0
SubcomponentHeaders is an array of Field names. ALL of my fields have captions, but only the field names get listed. Here is what I get when I enumerate the property names and valued for the "Minor_ID" field (the caption for which is "Subcomponent Identifier"):

Value H12-170
Attributes 34
CollatingOrder 1033
Type 10
Name Minor_Name
OrdinalPosition 1
Size 255
SourceField Minor_Name
SourceTable Subcomponents
ValidateOnSet False
DataUpdatable True
ForeignName
DefaultValue
ValidationRule
ValidationText
Required False
AllowZeroLength False
FieldSize
OriginalValue
VisibleValue
GUID ????????

Edit by Moderator:
Please start your own thread to ask your own questions. Post relevant links to other threads where necessary.

Thank you.

Insert New Records Into MSAccess Database Table.
Hi I am using DAO3.5 library.I am trying to add records into MSAccess database table.Does any one have Code samples? I have multiple field information to be entered.

Thanks.

Adding Field To Msaccess Table At Run Time Through VB
hello,

i have created a table which has x number of fields. However at run time i have a form which accepts additional fields which need to be added to the table structure. Is this possible? If yes, then how..or if no..then is there any other alternative way how i can i do it?


also, using crystal reports i have generated a reprt..now in this report i want to display two different sections of data. I mean i will be dispaying data from two different unrealted queries and tables. Is this possible?



gayatri

How To Check If Table/Field Exist In MSaccess
How do you check if table exist and or field exist in
ms access database?
(through coding)
before connecting to recordset ...

Unable To Insert Address Into The MSAccess Table
Hi all,
I am getting a new Error msg while inserting an address into a Table...
Here is the Code:
Site Address:"2nd Cross,I main,B'lore-19"

code: Sq="Insert into SiteWise1 values('" & SiteName & "','" & SiteAddress & "');"
db.Execute(Sq)

I think, the SingleCote(') giving this problem...
Can u pls help me to solve this problem..
thanks
from
Manu

Adding Field To Msaccess Table At Run Time Through VB
hello,

i have created a table which has x number of fields. However at run time i have a form which accepts additional fields which need to be added to the table structure. Is this possible? If yes, then how..or if no..then is there any other alternative way how i can i do it?


Thank You,

Lalitha.C

:MSACCESS: ALTER TABLE ADD COLUMN? How To ALLOW NULL
Hi,

in my code, i create new columns in a table with "ALTER TABLE ADD COLUMN" statements through ADO.

I use a MSACESS database.

But now, i also need to determine when i create new columns that the columns may contain empty values (null), and -for now-i don't see a way to do this through SQL-ADO.


Does anyone know a way how to achieve this wthout using ADOX?

I would be grateful if anyone could help me or point me to the right direction.


Regards,

Filip

His Royal Dudenss

Edited by - His_Royal_Dudeness on 2/8/2006 8:26:02 AM

Creating Table In MsAccess Using ADOX (Squence Problem )
hi,
Can anybody help me out how to keep if squence of column in new table as same as old table.

I am checking two different database in MsAccess, and if any table is missing in older database then i am creating it using ADOX ( it's a loop till all tables doesn't get check ).

In coding i am getting new table name, columns and all properties, then runtime creating a Statement (i.e. Create Query) but i could not maintain the squence of columns that is there in new database

That is if new database table has squence like
TableName :- TestTable
Column :- TestNo
Column :- TestName
Column :- Remarks

After creating the table in old database the squnece changes to
TableName :- TestTable
Column :- Remarks
Column :- TestName
Column :- TestNo

It creates a table column in ascending order.

Can anybody help me out how to maintain the squence of column in new table as same as old table

Codes For CONVERTING A Textfile Into An MSAccess Table/Database
Anybody who knows how to CONVERT a textfile into an MSAccess database/table using VB Codes? There is another way which is TRANSFERING data from the Textfile to MSAccess table but it's taking too much time that is why i wanna know how to CONVERT the textfile into an Access table/database. Thanks a lot!!!

Sending A MSAccess Query As XLS Via Email To Recipients From An Access Database Table
Hi,

I'm not a programmer but been given a task at work to change the way our Access Database's email an xls file thats created from a query. Currently the databases are using Lotus Notes to email the file but i need to change it to Outlook.

I can get an Access macro to simply do this but I need more done than just sending the file such as; a temp folder is created, the query runs and exports to this folder as an excel file, a message box appears pausing the whole task and allowing the user to edit the xls file, once complete the user presses 'ok' to continue and the xls file is attached to a new email message.

The VB code that works ok is;

DoCmd.SendObject acQuery, "BCG", "MicrosoftExcel(*.xls)", "me@me.com", "Subject", "Please find attached daily deal confirmation:", True, ""

But I need to attach the file from the temp folder in case it needs editing and pick a list of email addresses from an Access table called 'EmailRecipients'.

Here is the all the code, everything works fine up until
'Connect to Lotus Notes
...............................:



Option Compare Database
Option Explicit

Const mcTEMP_PATH = "p:Temp"

' Saves a query to a temporary file and sends via email.
Public Sub SendFile(ByVal loObjectType As AcObjectType, ByVal lsObjectName As String, ByVal lsSendGroup As String, ByVal lbPause As Boolean)
' Lotus Notes objects
Dim objDB As Object
Dim objSession As Object
Dim strServer As String
Dim strMailFile As String
Dim objDoc As Object
Dim objRichTextItem As Object
Dim objEmbed As Object

' Recordset and local storage objects
Dim lrsEmailDetails As New ADODB.Recordset
Dim lsMessage As String
Dim lsSubject As String
ReDim laSendTo(0) As Variant
ReDim laCopyTo(0) As Variant
ReDim laBlindCopyTo(0) As Variant

' File handling objects
Dim loFSO As New Scripting.FileSystemObject
Dim lsTempFile As String

lrsEmailDetails.Open "SELECT * FROM qryEmailDetails WHERE SendGroup = '" & lsSendGroup & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
If lrsEmailDetails.EOF Then
MsgBox "No email information available for this send", vbCritical
Exit Sub
End If

' Create the attachment, if there is one.
lsTempFile = ""
If lsObjectName > "" Then
' Check the "temp" folder exists and if not, then try to create it.
If Not loFSO.FolderExists(mcTEMP_PATH) Then
If MsgBox("Temp folder " & mcTEMP_PATH & " does not exist, create?", vbOKCancel) = vbOK Then
loFSO.CreateFolder (mcTEMP_PATH)
Else
Exit Sub
End If
End If

lsTempFile = loFSO.BuildPath(mcTEMP_PATH, FixName(lsObjectName & ".xls"))
DoCmd.OutputTo loObjectType, lsObjectName, acFormatXLS, lsTempFile

If lbPause Then
If MsgBox("The temporary file " & lsTempFile & " has been created and may be edited at this time." & vbCrLf & vbCrLf & "Once editing is complete, close the spreadsheet and press OK to continue", vbInformation + vbOKCancel) = vbCancel Then Exit Sub
End If
End If

' Connect to Lotus Notes
Set objSession = CreateObject("Notes.NOTESSESSION")
strServer = objSession.GETENVIRONMENTSTRING("mailserver", True)
strMailFile = objSession.GETENVIRONMENTSTRING("mailfile", True)
Set objDB = objSession.GETDATABASE(strServer, strMailFile)

lrsEmailDetails.MoveFirst
While Not lrsEmailDetails.EOF
lsMessage = n2b(lrsEmailDetails("Message"))
lsSubject = n2b(lrsEmailDetails("Subject"))
If lrsEmailDetails("Type") = "To" Then
ReDim Preserve laSendTo(UBound(laSendTo) + 1)
laSendTo(UBound(laSendTo)) = n2b(lrsEmailDetails("EmailAddress"))
ElseIf lrsEmailDetails("Type") = "CC" Then
ReDim Preserve laCopyTo(UBound(laCopyTo) + 1)
laCopyTo(UBound(laCopyTo)) = n2b(lrsEmailDetails("EmailAddress"))
ElseIf lrsEmailDetails("Type") = "BCC" Then
ReDim Preserve laBlindCopyTo(UBound(laBlindCopyTo) + 1)
laBlindCopyTo(UBound(laBlindCopyTo)) = n2b(lrsEmailDetails("EmailAddress"))
End If

lrsEmailDetails.MoveNext
Wend

' Create an email
Set objDoc = objDB.CREATEDOCUMENT
Set objRichTextItem = objDoc.CREATERICHTEXTITEM(objDoc, "Body")

objRichTextItem.AppendText lsMessage & vbCrLf & vbCrLf
objDoc.Subject = lsSubject
objDoc.SendTo = laSendTo
objDoc.CopyTo = laCopyTo
objDoc.BlindCopyTo = laBlindCopyTo
If lsTempFile <> "" Then
objRichTextItem.EMBEDOBJECT 1454, "", lsTempFile, "Attachment"
End If

objDoc.SaveMessageOnSend = True 'send E-mail
objDoc.SEND False 'false for do not attach a form


' cleanup the old file
If lsTempFile <> "" Then loFSO.DeleteFile (lsTempFile)
End Sub

' Convert Nulls to blanks
Private Function n2b(ByVal aVal) As String
If IsNull(aVal) Then
n2b = ""
Else
n2b = aVal
End If
End Function

' Replace spaces with underscores
Private Function FixName(ByRef lsName As String) As String
FixName = Replace(lsName, " ", "_")
End Function
---------------------------------------------------------------

I know this is probably quite a lot to ask but any suggestions or if anyone could point me in the right direction for further information then i'd be very very grateful.

Many thanks
Jrb.

Retrieve Table Names
I'm trying to create a sql statement that returns all the table names in a particular database or loops through the names so that I can store them into an array. Then I can go back and search each table for the existence of a record. Any ideas on how I can retrieve these table names?

Thanks!

How To Retrieve 8-bit BMP Color Table? -- Please Help
I am currently playing around with the following code:


Code:

Open strFile For Binary Access Read As #t
Get #t, , InfoHeader
Close #t

Colors = InfoHeader.bmiColors



Am I barking up the right tree?

My goal is to have a very simple program that has a listview into which I can drop bitmaps and immediately have a message box appear which tells me the color table like this:

1st color: (white or whatever)
2nd color: (red or whatever)
and so on

there are only five possible colors: red, black, white, blue, green

any thoughts??

all help will be greatly appreciated, thanks in advance even

- Jake

Retrieve Constraint Name For A Table

hi

i need to know can i in vb or sql, retrieve the name of a constraint for a table.
i need it because i want to delete in table the key, so constraints.

alter table table1 drop constraint constraintname

the problem that i need to retrieve this "constraintname"
i know how to retrieve specific data on table :
FieldInfo(0) = tdSource.Fields(X).Name
FieldInfo(1) = tdSource.Fields(X).Type
FieldInfo(2) = tdSource.Fields(X).Size
FieldInfo(3) = tdSource.Fields(X).Attributes

how can i retrieve constraint name?



Retrieve Table Names?
im building a database that needs to allow the users to add tables to the database. What I need to do is be able to retieve the table names from the database so that it can display a list of the tables in a combo box so the user can select what table they want to use, so how would i go about retrieving the table names?

The program is using a data control and a access file for the data.

Retrieve Constraint Name For A Table
hi

i need to know can i in vb or sql, retrieve the name of a constraint for a table.
i need it because i want to delete in table the key, so constraints.

alter table table1 drop constraint constraintname

the problem that i need to retrieve this "constraintname"
i know how to retrieve specific data on table :
FieldInfo(0) = tdSource.Fields(X).Name
FieldInfo(1) = tdSource.Fields(X).Type
FieldInfo(2) = tdSource.Fields(X).Size
FieldInfo(3) = tdSource.Fields(X).Attributes

how can i retrieve constraint name?

How To Retrieve Field From Table Using Combobox
I couldn't get the listing from the table using the combobox? Can somebody help?

My code is this:

Private Sub cboCompany_Click()
AdodcCustomers.Recordset.Find "Company = '" & cboCompany.Text & "'"
AdodcCustomers.Recordset.Filter = "Company = '" & cboCompany.Text & "'"
AdodcCustomers.Recordset.Filter = ""
End Sub

How To Retrieve Foxpro Table Structure?
Hi!

I need to convert a FoxPro database for MySQL. Ok.
I have FoxPro and MySQL Data providers installed on my machine, and can open both.

the problem is that I dont know the original FoxPro table internal structure.
How can I retrieve the datafield names, types and sizes?

PS: I am using ADO.

Thanks a LOT!

How To Connect To Ms Access And Retrieve A Table
hi! im new to vb 6. i want to connect my project to ms access xp and retrieve the entire table. i have a form named form1 and a button named button1. my database is located in c:database. my table name is NATION. what i want to do is to connect to the ms access xp and when you click the button1, it will retrieve or show the NATION table from my database as simple as that thanks=)

Need To Retrieve Minimum 3 Dates From The Table
Hi,

I am making one program in which I had set average. If the average is 3 and pending Orders are more than 3 then I need to select smaller 3 date's data from the table.

I am confused here. If you have any idea on this then pls do respond...!!!

Your all views and recommendations will be highly appriciated.

Regards,
Rinku

Retrieve Access Table Names With ADO
Hi all!

I am trying to use ADO to connect to an “unknown” MS Access database and then to allow the user to select the desired table in this database.

I want the user to be able to select at execution time the MS Access database of their choice and then open the table of their choice.

I am able to load an MS Access database successfully.  However, I am stumped on how to obtain the available tables in the database.

Any help would be greatly appreciated.

Thanks!


Can't Retrieve All Rows From Database Table
For some reason, I can't get all my table rows returned when I write my SQL Query. For example:

Select * from TABLE_NAME returns 2200 rows when there are 2500
Select ID from TABLE_NAME returns all 2500

Any idea why? Is there a size limitation to my variant variable? My code is as follows:

    Dim SQLString, ConnectionString As String
    Dim Results As Variant
            
    ConnectionString = "ODBC;Driver={SQL Server};Server=SERVER;Database=DATABASE;UID=hello;PWD=world;"
    
    SQLString = "SELECT * FROM TABLE_NAME"

    Results = Run("getSQLServerRows", SQLString, ConnectionString, , , 1) <-- simple macro that executes sql code

    Worksheets("Sheets").Range("A1:U" & (UBound(Results) + 1)) = Results
   
    ......

When I open up my locals window and look at what has been returned, not all the rows are there. This happens intermittantly depending on the table. Might work fine for one table (13000 rows, 20 columns), but doesn't work for others (2500 rows, 49 columns).

Any help or suggestions to fix would be appreciated.

Thanks.

Table Names Retrieve Using Odbc
HI,
I got an odbc connection to Access and to Sql Server. My task is to created connection strings and retrieve tables names; among other duties. Both connections are fine, I managed to retrieve table names from Sql Server using odbc but I can't figure out how to do so in MS Access since I have no or little experiece using it . I would appreciate if anyone can help me with this issue thks

Copyright 2005-08 www.BigResource.com, All rights reserved