Writing to an Access or Excel file 
Author Message
 Writing to an Access or Excel file

Dear Josh,

    There are two ways to do this; either through coding in SQL or through
corresponding a form in VB to a table in Access.  In my opinion, since you
don't know
exactly what file you want to write do; albeit an Access or Excel file and
since coding in SQL can be tricky versus the other, I'll give you a template
on how to code this problem in SQL assuming you are writing to an Access

1.  Declare a Global (Const) variable to hold a string value of where your
Access file resides in a module delcarations section.
    Example: Global Const DB_PATH = "C:\Temp\db1.mdb"
2.  Then make a module with any arguments you want to pass.
    Public Sub someSub(someArgs as someDataType)

    End Sub
3.  Declare the following variables in your sub.
    dim dbs as Database, rst as Recordset, sqlString as String
4.  Set your Sql String.
    If you want to add a record, use INSERT INTO statement. It looks like
    sqlString = "INSERT INTO someTable (field1, field2, etc.)" & _
                    "VALUES ('" & someform.yourcontrol.text & "', '" &
someform.checkbox.value & "')"
    5. Open the access database.
        set dbs = opendatabase(db_path)
    6. Execute your sql code.
    7. Close the database
    The complete template looks like this.
    Public Sub someSub()
    dim dbs as database, rst as recordset, sqlString as String
    sqlString = "INSERT INTO someTable (field1, field2, etc.)" & _
   "VALUES ('" & someform.yourcontrol.text & "', '" &
someform.checkbox.value & "')"
    set dbs = opendatabase(db_path)
    End Sub

****** It is imperative that you close your database after opening it.  If
you don't and have too many established sockets open to your database, your
program after a while because access only allows so many open sockets to it
at a time.

****** This coding can get very complicated, yet offers you a great deal of
control. You can code an app that uses an access back-end today, but will
also be ready in general to point to an SQL server if necessary.  However,
this is tough to learn.  I highly recommend that you take a course in VB at
a local university, or technical college, and GET A GOOD VB PROGRAMMING
BOOK.  These will pay you countless dividends in the future.


Jonathon P. Gladieux


>I just started learning VB and would like to send information to a database
>or spreadsheet.  The user of the application will select items from combo
>boxes and input information in text boxes.  Then within the click event for
>a command button I would like to send all of the input information to
>specific fields or cells.  I may be getting ahead of myself but I would
>really like to know the steps required to do this.

Tue, 02 Jan 2001 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Accessing/Writing to Excel files

2. VB6: Accessing/Writing to Excel files

3. Write a Excel File Without Excel App

4. Write to an Excel file from VB without Excel present

5. Writing Excel Files W/O Existing Excel Application

6. Writing Excel-files w/o Excel present?

7. read excel file in access 2000 lire fichier excel dans access 2000

8. ANNOUNCE : Platform independent toolkit for writing excel file format files

9. Opening an excel file through a form/running excel macros through access

10. Save embedded excel spreadsheet in access to excel file

11. Accessing an Excel file without opening Excel

12. Write data from excel to MS-Access using VBA


Powered by phpBB® Forum Software