how do I run queries in access on external data?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a database for a small company. This is the first time
I have attempted this at this level. I want to take data that is compiled by
middle management in excel worksheets and run queries in a secure database on
the information in the worksheets.
 
Hi Lisa,

Usually one can either use linked tables or write SQL queries using
syntax like this:

SELECT * FROM
[Excel 8.0;HDR=No;database=C:\TEMP\AddrNew3.xls;].[Sheet1$A10:E20];

The range part of the FROM expression can be also be
[SheetName$]
[RangeName]
You can use a single-cell range to get the value from a single cell as
if it were a table.

It's also possible of course to use VBA automation to open the workbook
and access or manipulate anything in it.

Be aware that data entered into Excel sheets by middle management is
likely to be riddled with inconsistencies that will prevent it being
cleanly linked or imported. There are also likely to be problems with
data types, with Access getting confused about what type to assign to
the fields it is linking. See
http://www.dicks-blog.com/excel/2004/06/external_data_m.html for
details.
 
Hi lisa,
I think you should use ado to connect to excel. ADO is a tool for making
recordsets from external/internal databases.
Now i have never connected to excel using ado before but i did some reserch
and this is what i got to work.

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\Test\Book1.xls;" & _
"Extended Properties='Excel 8.0;IMEX=1'"

sSQL = "SELECT Name, Dates " & _
"FROM [Sheet1$] "
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic

If rs.RecordCount > 0 Then
MsgBox rs!Name & " , " & rs!Dates
End If

If you have more questions about ADO, there is a ADO newsgroup in the "Data
Access" MSDN Newsgroups.
Also, one of the links i found helpful was:
http://support.microsoft.com/kb/257819/EN-US/

Hope this helps.
Good Luck!
 
Oops, if you want to edit the data then you need to
change:
"Extended Properties='Excel 8.0;IMEX=1'"
To:
"Extended Properties='Excel 8.0;IMEX=2'"

sorry!

visdev1 said:
Hi lisa,
I think you should use ado to connect to excel. ADO is a tool for making
recordsets from external/internal databases.
Now i have never connected to excel using ado before but i did some reserch
and this is what i got to work.

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\Test\Book1.xls;" & _
"Extended Properties='Excel 8.0;IMEX=1'"

sSQL = "SELECT Name, Dates " & _
"FROM [Sheet1$] "
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic

If rs.RecordCount > 0 Then
MsgBox rs!Name & " , " & rs!Dates
End If

If you have more questions about ADO, there is a ADO newsgroup in the "Data
Access" MSDN Newsgroups.
Also, one of the links i found helpful was:
http://support.microsoft.com/kb/257819/EN-US/

Hope this helps.
Good Luck!


Lisa said:
I am trying to create a database for a small company. This is the first time
I have attempted this at this level. I want to take data that is compiled by
middle management in excel worksheets and run queries in a secure database on
the information in the worksheets.
Click to expand...
Click to expand...
 
Thank you so much for the time and assistance. I will try and see what
happens. Have a great day.
Lisa

visdev1 said:
Oops, if you want to edit the data then you need to
change:
"Extended Properties='Excel 8.0;IMEX=1'"
To:
"Extended Properties='Excel 8.0;IMEX=2'"

sorry!

visdev1 said:
Hi lisa,
I think you should use ado to connect to excel. ADO is a tool for making
recordsets from external/internal databases.
Now i have never connected to excel using ado before but i did some reserch
and this is what i got to work.

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\Test\Book1.xls;" & _
"Extended Properties='Excel 8.0;IMEX=1'"

sSQL = "SELECT Name, Dates " & _
"FROM [Sheet1$] "
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic

If rs.RecordCount > 0 Then
MsgBox rs!Name & " , " & rs!Dates
End If

If you have more questions about ADO, there is a ADO newsgroup in the "Data
Access" MSDN Newsgroups.
Also, one of the links i found helpful was:
http://support.microsoft.com/kb/257819/EN-US/

Hope this helps.
Good Luck!


Lisa said:
I am trying to create a database for a small company. This is the first time
I have attempted this at this level. I want to take data that is compiled by
middle management in excel worksheets and run queries in a secure database on
the information in the worksheets.
Click to expand...
Click to expand...
Click to expand...
 
John,

Thank you so much for your time and assistance. I will try it and see what
happens. Have a great day! Also, thanks for the tip of information from
middle management.

Lisa

John Nurick said:
Hi Lisa,

Usually one can either use linked tables or write SQL queries using
syntax like this:

SELECT * FROM
[Excel 8.0;HDR=No;database=C:\TEMP\AddrNew3.xls;].[Sheet1$A10:E20];

The range part of the FROM expression can be also be
[SheetName$]
[RangeName]
You can use a single-cell range to get the value from a single cell as
if it were a table.

It's also possible of course to use VBA automation to open the workbook
and access or manipulate anything in it.

Be aware that data entered into Excel sheets by middle management is
likely to be riddled with inconsistencies that will prevent it being
cleanly linked or imported. There are also likely to be problems with
data types, with Access getting confused about what type to assign to
the fields it is linking. See
http://www.dicks-blog.com/excel/2004/06/external_data_m.html for
details.


I am trying to create a database for a small company. This is the first time
I have attempted this at this level. I want to take data that is compiled by
middle management in excel worksheets and run queries in a secure database on
the information in the worksheets.
Click to expand...
Click to expand...
 
Any time lisa.
I enjoyed the challenge.

Lisa said:
Thank you so much for the time and assistance. I will try and see what
happens. Have a great day.
Lisa

visdev1 said:
Oops, if you want to edit the data then you need to
change:
"Extended Properties='Excel 8.0;IMEX=1'"
To:
"Extended Properties='Excel 8.0;IMEX=2'"

sorry!

visdev1 said:
Hi lisa,
I think you should use ado to connect to excel. ADO is a tool for making
recordsets from external/internal databases.
Now i have never connected to excel using ado before but i did some reserch
and this is what i got to work.

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\Test\Book1.xls;" & _
"Extended Properties='Excel 8.0;IMEX=1'"

sSQL = "SELECT Name, Dates " & _
"FROM [Sheet1$] "
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic

If rs.RecordCount > 0 Then
MsgBox rs!Name & " , " & rs!Dates
End If

If you have more questions about ADO, there is a ADO newsgroup in the "Data
Access" MSDN Newsgroups.
Also, one of the links i found helpful was:
http://support.microsoft.com/kb/257819/EN-US/

Hope this helps.
Good Luck!


:

I am trying to create a database for a small company. This is the first time
I have attempted this at this level. I want to take data that is compiled by
middle management in excel worksheets and run queries in a secure database on
the information in the worksheets.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top