Importing/querying data from .mdb file into Excel

  • Thread starter Thread starter jimmy shaker.
  • Start date Start date
J

jimmy shaker.

Hello everyone, here's my situation...

I have a Microsoft Access database file that contains information on
computer systems, such as their computer names, their IP addresses,
CPU model, POC name, etc.

What I want to do with an Excel file is that when I type the computer
name in a cell, Excel will then query the .mdb file and automatically
fill in other cells with the POC name, IP address, and CPU model of
that particular computer system that is contained in the .mdb file.

Question #1
Is this possible?

Question #2a
If it is possible, what would be the best means of doing this??

Question #2b
If it's not possible, what would be an alternative to doing what I
need it to do (querying the .mdb for information based on a computer
name)?

Thanks in advance for any help you can provide.

-js.
 
Forgot to mention one thing...when the Excel file queries the Access
database, the POC name, IP address, etc will go to certain cells
within the spreadsheet if that matters any.

Thanks again,
js.
 
Hi Js, theres a way to doit either by using DAO or ADO I will like to know
how is your level of knowleadge in VBA in excel in order to direct my answer
more appropiate.
Jose Rojas
 
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Jose,

Thanks for the reply back. Unfortunately, my knowledge level in VBA
is at zero, but I will learn it to get this working. I just didn't
know what avenue to take to get this started.

I'll start researching DAO and ADO in the meantime.

Thanks again!
js.
 
'ok thats good
'lets begin from zero then
'
'first in a lets say the sheet where you want to type the pc name, is sheet1
'the name of your database is PCData
'the name of tha table to look up is PCInformation
'
'If none of these names are what you want then modify to represent your
needs
'On sheet 1 we may have headers on row1 so we will begin from cell a2
'
'Open the VB Editor by pressing alt+F11
'
'goto Insert >>>> Module
'goto Tools >>>> Refences find and select "Microsoft DAO 3.51 ojbect
library" or the latest one that you find in there.
'
'Once in the Module type the following

Option Explicit

Public Const DbName As String = "Type Databse Path and Name Here"


Sub GetDataFromDB()
Dim Db1 As Database
Dim Rs1 As Recordset
Dim POCName As String
Dim IPAddress As Long
Dim CPUModel As String
Dim LastRow, FirstRow As Variant
Dim PcName As String
Dim i As Integer


Set Db1 = DBEngine.OpenDatabase(DbName) 'Open Connection to Database

FirstRow = Range("A1").End(xlUp).Row + 1 'Find Begin of cells because of the
headers wi will begin on row 2
LastRow = Range("A10000").End(xlUp).Row 'Find where to stop

For i = FirstRow To LastRow
PcName = Cells(i, 1).Value
Set Rs1 = Db1.OpenRecordset("Select * From PCInformation Where PCName
like '" & PcName & "'", dbOpenSnapshot) 'Open The Recordset
Cells(i, 2).Value = Rs1.Fields("POCName").Value
Cells(i, 3).Value = Rs1.Fields("IPAddress").Value
Cells(i, 4).Value = Rs1.Fields("CPUModel").Value
Next i

Set Db1 = Nothing
Set Rs1 = Nothing

End Sub
 
Back
Top