I use VB6.0 and Access

  • Thread starter Thread starter diaExcel
  • Start date Start date
D

diaExcel

I want to put the data from an Excel file in an Access database using VB6.0
(ADO), how can I do that?

Thank you.
 
Hi,

I want to import the Excel file in Access database using ADO in VB6.0, can I
do that and how?

Thank you.

"Alex Dybenko" a scris:
 
diaExcel said:
Hi,

I want to import the Excel file in Access database using ADO in VB6.0, can I
do that and how?

Thank you.
Click to expand...

You can connect to the Excel file through ADO to retrieve the
information, given that the information is in some kind of order.
Here's some results of a short web search

http://www.ozgrid.com/forum/showthread.php?t=17158
http://support.microsoft.com/kb/326548/en-us
http://support.microsoft.com/kb/262537/en-us

Let's take it a step further. Say you've got the file xlsfile.xls
somewhere, where the headers are stuffed at the top of the sheet (sheet
named xlsfile, too), I think you should be able to do something like
this:

Dim cn As ADODB.Connection
Dim RecsAffected As Long
Dim SQLString As String

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\<path and name of>.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes'"
cn.Open
SQLString = "INSERT INTO TargetTable In " & _
"'c:\<path and name of>.mdb' " & _
"SELECT * FROM [xlsfile]"
cn.Execute SQLString, RecsAffected, adCmdText + adExecuteNoRecords
Debug.Print RecsAffected

I think you can also be a bit selective with ranges, say

...SELECT * FROM [xlsfile$A1:X120]"

xlsfile here represenging sheet name.
 
Thank you so much!!!
I have another question....
in VB6.0 in a form I have a DataGrid which is populated with data from
Access, but now I want to add all rows from this datagrid in a different
table in Access, how can I do that, because I've seen that I have to add row
by row and I don't want to in that way, I want that all the rows in a single
step to be added...

Thank you

"RoyVidar" a scris:
diaExcel said:
Hi,

I want to import the Excel file in Access database using ADO in VB6.0, can I
do that and how?

Thank you.
Click to expand...

You can connect to the Excel file through ADO to retrieve the
information, given that the information is in some kind of order.
Here's some results of a short web search

http://www.ozgrid.com/forum/showthread.php?t=17158
http://support.microsoft.com/kb/326548/en-us
http://support.microsoft.com/kb/262537/en-us

Let's take it a step further. Say you've got the file xlsfile.xls
somewhere, where the headers are stuffed at the top of the sheet (sheet
named xlsfile, too), I think you should be able to do something like
this:

Dim cn As ADODB.Connection
Dim RecsAffected As Long
Dim SQLString As String

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\<path and name of>.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes'"
cn.Open
SQLString = "INSERT INTO TargetTable In " & _
"'c:\<path and name of>.mdb' " & _
"SELECT * FROM [xlsfile]"
cn.Execute SQLString, RecsAffected, adCmdText + adExecuteNoRecords
Debug.Print RecsAffected

I think you can also be a bit selective with ranges, say

...SELECT * FROM [xlsfile$A1:X120]"

xlsfile here represenging sheet name.
Click to expand...
 
answer to your second question
i do it this way:
make a database in acces with the exact number of rows and form as you need
it
than select all the data you want to replace (use ctrl C)
go to the empty database and select all (left up) and do ctrl V
i use this methode to place excell data into acess database, it works perfekt
succes
Thank you so much!!!
I have another question....
in VB6.0 in a form I have a DataGrid which is populated with data from
Access, but now I want to add all rows from this datagrid in a different
table in Access, how can I do that, because I've seen that I have to add row
by row and I don't want to in that way, I want that all the rows in a single
step to be added...

Thank you

"RoyVidar" a scris:
[quoted text clipped - 37 lines]
xlsfile here represenging sheet name.
Click to expand...
Click to expand...
 
don't use vb 6 for this
make an empty acces database with the exact number of rows as you have in
excel
select all the data (in excel) you want to move (ctrl+C)
go to acces database en select all (upper left corner) and do ctrl+V
i use this methode on an database with 15.000 ithems on 29 rows - perfect
succes
 
Thank you so much!!!
I have another question....
in VB6.0 in a form I have a DataGrid which is populated with data
from Access, but now I want to add all rows from this datagrid in a
different table in Access, how can I do that, because I've seen that
I have to add row by row and I don't want to in that way, I want
that all the rows in a single step to be added...

Thank you
Click to expand...

[snipped stuff related to previous question]

Dear diaExcel,

though there probably are poeple frequenting this NG that are familiar
with objects of classic VB, I think you probably have better chances
of getting a reply in an NG dedicated to classic VB.

I don't know anything about the control you mention, as it doesn't
exist
in the product for which this NG is dedicated (Ms Access), but, say
you've populated it trough a select (apply WHERE clause as appropriate)

SELECT col1, col2, ...colN FROM SomeTable

you should be able to do something like (air code)

INSERT INTO NewTable
SELECT col1, col2, ...colN FROM SomeTable

- using the first select.

Or, to create a new table, something like

SELECT col1, col2, ...colN
INTO NewTable
FROM SomeTable

You should be able to fire some of this off on an ADO connection to
the db.

But adding redundant data etc, begs the question whether you've chosen
the most optimal design.
 
Back
Top