Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get names from Access table
Good morning,
Not being a programmer, but a dabbler i am not sure if the following is possible ? Retrieve names from a table (tblQualityStaff)in Access file (New.mdb)to insert into a spread sheet in Excel. If it is possible, could you help with some code please. Thanks in Advance Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get names from Access table
You don't actually need code. (If you want to use code then look up ADO DB
Recordset). Go to Data - Import External Data - Data Base Query... then just follow the wizard. -- HTH... Jim Thomlinson "Tempy" wrote: Good morning, Not being a programmer, but a dabbler i am not sure if the following is possible ? Retrieve names from a table (tblQualityStaff)in Access file (New.mdb)to insert into a spread sheet in Excel. If it is possible, could you help with some code please. Thanks in Advance Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get names from Access table
Hi Jim, thanks for the reply, but i want to do it as part of other code
that i am using to manipulate a spreadsheet so therefore do not want to do this manually Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get names from Access table
Here is a function that runs a query against a database. You need to
understand the select statement to use this. You must also reference the Microsoft ADODB Recordset in the VBE. The function returns a recordset object that you can use. m_cDBLocation is a module level variable which defines the name and location of the database. Private m_cDBLocation as string = "C:\New.mdb" Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _ ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean) As ADODB.Recordset Dim strConnection As String strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & m_cDBLocation & ";" Set RunQuery = New ADODB.Recordset With RunQuery .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic End With RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " & strOrderBy, strConnection, , , adCmdText If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing End Function The function is used something like this Dim rst As ADODB.Recordset Dim strSelect As String Dim strFrom As String Dim strWhere As String Dim strOrderBy As String 'Create SQL statement strSelect = "SELECT Max(tblVehicleHeader.[Unit Number]) AS [MaxOfUnit Number]" strFrom = "FROM tblVehicleHeader" strWhere = "" strOrderBy = ";" Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, True) The final argument specifies that I want a connected recordset which allows me to write back to the database using .update -- HTH... Jim Thomlinson "Tempy" wrote: Hi Jim, thanks for the reply, but i want to do it as part of other code that i am using to manipulate a spreadsheet so therefore do not want to do this manually Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get names from Access table
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get names from Access table
Hi: Tempy
Here is another example of ADO code, along with Jim's it should give you a good understanding of the process. This example retrives data from the nwind db that comes with Access. Cut and paste the procedure and follow the comments. Good Luck TK .................................................. ........ Private Sub CommandButton4_Click() On Error GoTo ErrHandler Dim rg As Range Set rg = ThisWorkbook.Worksheets(2).Range("a1") 'To use ADO objects in an application add a reference 'to the ADO component. From the VBA window select 'Tools/References< check the box ' "Microsoft ActiveX Data Objects 2.x Library" 'You should fully quality the path to your file Dim db_Name As String db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb") Dim DB_CONNECT_STRING As String DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "data Source=" & db_Name & ";" & ", , , adConnectAsync;" 'Create the connection Dim cnn As New ADODB.Connection Set cnn = New Connection cnn.Open DB_CONNECT_STRING 'Create the recordset Dim rs As ADODB.Recordset Set rs = New Recordset 'Determines what records to show Dim strSQL As String strSQL = "SELECT CompanyName, ContactName, City, Country " & _ "FROM Customers ORDER BY CompanyName" 'Retreive the records rs.CursorLocation = adUseClient rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic 'Test to see if we are connected and have records Dim num As Integer num = rs.RecordCount Dim num1 As Integer num1 = rs.Fields.Count If cnn.State = adStateOpen Then MsgBox "Welcome to! " & db_Name & " Records = " & num & " Fields = " & num1, vbInformation, _ "Good Luck TK" Else MsgBox "Sorry. No Data today." End If 'Copy recordset to the range rs.MoveLast rs.MoveFirst rg.CopyFromRecordset rs rg.CurrentRegion.Columns.AutoFit 'close connection cnn.Close Set cnn = Nothing Set rs = Nothing Exit Sub ErrHandler: MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly End Sub .................................................. ............. "Tempy" wrote: Thanks Jim Tempy *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Spreadsheet from Access. List of names changes as names are | Excel Discussion (Misc queries) | |||
Fetch list of table names in access to a record set | Excel Programming | |||
Get table names from access through vba in excel | Excel Programming | |||
How can I get all table names from MS Access databse | Excel Programming | |||
retrieving table names from Excel and Access sources using ADO | Excel Programming |