473,836 Members | 1,599 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Getting data from Access DB while in excel.

I have an established secured access database. I would like to read
fields in the Access DB while im in excel via the external database
query tools. However im getting an error about not having permission
to the db. I enter my crudentials and I still get the error. Any ideas
on how to go about this?

Sep 6 '07 #1
2 1932
On Sep 7, 5:45 am, radink <radi...@gmail. comwrote:
I have an established secured access database. I would like to read
fields in the Access DB while im in excel via the external database
query tools. However im getting an error about not having permission
to the db. I enter my crudentials and I still get the error. Any ideas
on how to go about this?
Off the cuff, I would say you'd want to create an ODBC DSN name on the
machine (see Administrative Tools Data Sources (ODBC) on WXP and
specify the filename, workgroup file, credentials. Then using the
external database query tools connect via that DSN.

Sep 6 '07 #2
On Sep 6, 2:45 pm, radink <radi...@gmail. comwrote:
I have an established secured access database. I would like to read
fields in the Access DB while im in excel via the external database
query tools. However im getting an error about not having permission
to the db. I enter my crudentials and I still get the error. Any ideas
on how to go about this?
Here's a shot in the dark. I noticed that getting Access data from
Excel requires some delicate handling of the Recordset object because
it doesn't know what CurrentDb means. After setting a reference in
Excel to the DAO Object library I tried:

Sub Macro1()
Dim MyDB As Database
Dim MyRS As Recordset
Dim strSQL As String

strSQL = "SELECT * FROM MyTable;"
Set MyDB = Workspaces(0).O penDatabase("C: \Test.mdb")
Set MyRS = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)
MyRS.MoveFirst
MsgBox (MyRS("MyField" ))
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Sub

Note that I haven't addressed the security part. The OpenDatabase
method has a 'connect' variant "(String subtype) that specifies
various connection information, including passwords."

I'm not sure what the external database query tools are. Perhaps you
can teach me about them?

I don't know if anyone will find the code useful or not. Note that
Personal.xls will probably need to be used if the macro needs to work
within all spreadsheets. I think that Personal.xls needs to be
unhidden, macro edited, saved and then hidden again.

James A. Fortune
CD********@Fort uneJames.com

Sep 7 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
9251
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works only exporting to single worksheet. but i need to export data to multiple worksheets. it is very urgent to us. so please help me in code.
1
17422
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed a database for her which is intended to make things a lot easier; however, I don't have a lot of experience with Access and I find that designing the reports in Access is tedious. I want to be able to print the reports (which are simply based on...
3
25062
by: Mads Petersen | last post by:
I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown. eg. this only transferes the first record in the area. ..Fields("Uge").Value = ws.Range("A98").Value Sub SelectMaster()
0
2842
by: Jack | last post by:
Windows 2K Pro Access/Excel 2003 Hi there, I have a large number of password protected Excel Workbooks. The files are protected by code that runs when the workbook opens. The code asks the user to key in a password into an input box, the password is compared to a a partciular cell on a hidden (xlVeryHidden) sheet and if it matches the Workbooks opens. I have not used the usual Excel password protection as I need to provide for several...
11
11297
by: Hi5 | last post by:
Hi, I am new to access I usedto work in Oracle and Mysql. I am after a way that enables me to populate a database I designed in access with lots of data which can be sorted in excel sheets, The source of all data is a very large size (100*2000) excel spread sheet In which some data types are absulotely Irelevant to access data types in my DB there fore I changed It to a number of excel sheets
6
6376
by: syvman | last post by:
Hi everyone... I am pulling my hair out trying to do this, and was wondering if someone could give me some assistance... I have an Excel spreadsheet containing several worksheets. I'd like to be able to take all of the data from the second column (B) of each worksheet and append that raw data to an access table. The columns in the spreadsheet do not have headers for use as field names (it's my assumption that my table should have a...
2
24716
by: Robert Dyke | last post by:
Hey ... I've got about 500 Excel files with data stored in them in tabular format ... (my coworker used excel as a database). I want to write a vb6 program that will open up these files and get the info from each row, and store it in an MS Access database, or perhaps a comma-delimited file. How can I get into an excel file with VB6? I have VB6 enterprise, and I've installed the "excel" data access components.
0
1007
by: jetaw03 | last post by:
guys, i need help in using VB 6.0.can you help me to get a data from an excel file from ms access and put it in ms excel..pls help me guys.. here is the sample of the file in access: sum1 sum2 22321 123123 here is a sample of the excel file: SUM1
3
7170
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this file but save it as an excel file. The data in this excel file will be imported into an Access database. The
0
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10596
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10255
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9382
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7793
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6980
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.