Connection from Excel to Access

I am developing an application in Excel that reads data from and writes data 
to an Access database. I am using VBA and have experimented a bit with 
various connection strings - without really knowing what I'm doing. Could 
someone please point me to some usefull examples? - or perhaps show the best 
(most reliable and fastest) connection string to use?

Thanks in advance!
Helge 


0
Helge
11/25/2009 4:27:33 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

2 Replies
700 Views

Similar Articles

[PageSpeed] 52

On Nov 25, 10:27=A0am, "Helge V. Larsen"
<Helge.V.Larsen_Remove_this@Risoe_Remove_this.DK> wrote:
> I am developing an application in Excel that reads data from and writes d=
ata
> to an Access database. I am using VBA and have experimented a bit with
> various connection strings - without really knowing what I'm doing. Could
> someone please point me to some usefull examples? - or perhaps show the b=
est
> (most reliable and fastest) connection string to use?
>
> Thanks in advance!
> Helge

Heres some of my code that has excel connecting to an access database
and inserting a record base on data entered on a form

Dim db As DAO.Database
Dim strAdd_English As String
Dim strAdd_French As String
Set db =3D OpenDatabase("I:\Access\Client Freezes\Client Freeze.mdb")
user_id =3D InputBox("Enter User ID")

With db
    .Execute "INSERT INTO [Master Log] ( [Load Date], letter_db_date,
due_date_db, Area_Name, Letter_Date, [Plan ID], [Plan Desc], [Plan
Type], Salutation, [Client ID]," _
& "  [Client First Name], [Client Last Name], Client_Full_name,
addr_line1, addr_line2, addr_line3, addr_city_nam, addr_postal_cde,
srgn_abbr_nam, cnslt_sex, cnslt_title, " _
& " cnslt_id, cnslt_first_nam, cnslt_last_nam, region_office_id,
region_office_nam, OFFICE_phone_number, Entered_Phone_Number, bm_id,
bm_first, bm_last," _
& "user_id, [Analyst Name], [Analyst Number], trade_date, due_date,
Clie_lang_cde, Admin_Name, trade_type, Salutation_line ) " _
& "SELECT [Temp batch english].[Load Date], [Temp batch
english].Letter_DB_Date, [Temp batch english].due_date_db, [Temp batch
english].Area_Name," _
& " [Temp batch english].Letter_Date, [Temp batch english].[Plan ID],
[Temp batch english].[Plan Desc], [Temp batch english].[Plan Type],
[Temp batch english].Salutation," _
& " [Temp batch english].[Client ID], [Temp batch english].[Client
First Name], [Temp batch english].[Client Last Name], [Temp batch
english].Client_Full_name," _
& " [Temp batch english].addr_line1, [Temp batch english].addr_line2,
[Temp batch english].addr_line3, [Temp batch english].addr_city_nam,"
_
& " [Temp batch english].addr_postal_cde, [Temp batch
english].srgn_abbr_nam, [Temp batch english].cnslt_sex, [Temp batch
english].cnslt_title," _
& " [Temp batch english].cnslt_id, [Temp batch
english].cnslt_first_nam, [Temp batch english].cnslt_last_nam, [Temp
batch english].region_office_id," _
& " [Temp batch english].region_office_nam, [Temp batch
english].OFFICE_phone_number, [Temp batch
english].Entered_Phone_Number, [Temp batch english].bm_id, [Temp batch
english].bm_first," _
& " [Temp batch english].bm_last, [Temp batch english].user_id, [Temp
batch english].[Analyst Name], [Temp batch english].[Analyst Number],"
_
& " [Temp batch english].trade_date, [Temp batch english].due_date,
[Temp batch english].Clie_lang_cde, [Temp batch english].Admin_Name,"
_
& " [Temp batch english].trade_type , [Temp batch
english].Salutation_line" _
& " FROM [Temp batch english]" _
& " WHERE ((([Temp batch english].user_id)=3D'" & user_id & "'));"



    .Execute "INSERT INTO [Master Log] ( [Load Date], Area_Name,
Letter_Date, [Plan ID], [Plan Type], Salutation, [Client ID], [Client
First Name], [Client Last Name], Client_Full_name, addr_line1,
addr_line2, addr_line3, addr_city_nam, addr_postal_cde, srgn_abbr_nam,
cnslt_sex, cnslt_title, cnslt_id, cnslt_first_nam, cnslt_last_nam,
region_office_id, region_office_nam, OFFICE_phone_number,
Entered_Phone_Number, bm_id, bm_first, bm_last, user_id, [Analyst
Name], [Analyst Number], trade_date, due_date, Clie_lang_cde,
Admin_Name, trade_type, Salutation_line )" _
& " SELECT [Temp Batch French].[Load Date], [Temp Batch
French].Area_Name, [Temp Batch French].Letter_Date, [Temp Batch
French].[Plan ID], [Temp Batch French].[Plan Type], [Temp Batch
French].Salutation, [Temp Batch French].[Client ID], [Temp Batch
French].[Client First Name], [Temp Batch French].[Client Last Name],
[Temp Batch French].Client_Full_name, [Temp Batch French].addr_line1,
[Temp Batch French].addr_line2, [Temp Batch French].addr_line3, [Temp
Batch French].addr_city_nam, [Temp Batch French].addr_postal_cde,
[Temp Batch French].srgn_abbr_nam," _
& " [Temp Batch French].cnslt_sex, [Temp Batch French].cnslt_title,
[Temp Batch French].cnslt_id, [Temp Batch French].cnslt_first_nam,
[Temp Batch French].cnslt_last_nam, [Temp Batch
French].region_office_id, [Temp Batch French].region_office_nam, [Temp
Batch French].OFFICE_phone_number, [Temp Batch
French].Entered_Phone_Number, [Temp Batch French].bm_id, [Temp Batch
French].bm_first, [Temp Batch French].bm_last, [Temp Batch
French].user_id, [Temp Batch French].[Analyst Name], " _
& " [Temp Batch French].[Analyst Number] , [Temp Batch
French].trade_date, [Temp Batch French].due_date, [Temp Batch
French].Clie_lang_cde, [Temp Batch French].Admin_Name, [Temp Batch
French].trade_type, [Temp Batch French].Salutation_line " _
& " FROM [Temp Batch French] WHERE ((([Temp batch french].user_id)=3D'"
& user_id & "'));"

    .Execute "DELETE [Temp batch english].* FROM [Temp batch english]
WHERE ((([Temp batch english].user_id)=3D'" & user_id & "'));"
    .Execute " DELETE [Temp batch french].* FROM [Temp batch french]
WHERE ((([Temp batch french].user_id)=3D'" & user_id & "'));"
End With
0
Alt255
11/25/2009 5:16:39 PM
"Helge V. Larsen" <Helge.V.Larsen_Remove_this@Risoe_Remove_this.DK> wrote in 
message news:uZfpywebKHA.2184@TK2MSFTNGP04.phx.gbl...
>I am developing an application in Excel that reads data from and writes 
>data to an Access database. I am using VBA and have experimented a bit with 
>various connection strings - without really knowing what I'm doing. Could 
>someone please point me to some usefull examples? - or perhaps show the 
>best (most reliable and fastest) connection string to use?
>
> Thanks in advance!
> Helge

Try this site:

http://www.connectionstrings.com/

or this one:

http://www.carlprothman.net/Default.aspx?tabid=81

You should find what you want at either one.

HTH


0
Stuart
11/25/2009 5:53:20 PM
Reply:

Similar Artilces:

new to Excel- need Excel- dont have prior version- help
Hello, I need to buy a copy of Microsoft Excel. I do not have a prior version, just ms works' spreadsheet program. People have been sending me spreadsheets but I am unable to open them. Question- my local staples sells Excel 2003 Full Version for around 214 USD... Is this the cheapest way to go? I saw some 2000 Versions on Ebay- for lots less. Please advise... Also whats the cheapest place online to buy this? I did a Froogle and came up with some places I have never heard of. THank you KOS btw running XP Home Hi KOS If you only want to view/print you can use the free reader http://www...

EXCEL EXPORT PROBLEMS
I am setting up a catalogue for someone who has given me their price/product info. setup in EXCEL tables. They want to be able to make the necessary updates to their EXCEL tables and then send to me for updating future catalogues. I have tried to import into both Corel Draw and Word with no success, either the info. is non-editable once imported or it is converted into a mess. Can EXCEL files be imported/exported at all into any other layout programs while still maintaining the columns/tables set up in the original? I'd appreciate your help, thanks! ...

mailmerge in excel
using excel to set up forms since layout is so easy. now that xp office has so easy mail merge, is it possible to personalize or populate excel worksheets with other excel data? lookups will work, but mail merge would seem to be easier, by selecting the addressees and let 'r rip thanks Mail merge is in MS Word and is not going to populate an Excel worksheet. You could use a macro in Excel to use a template and to copy to selected cells, or to replace cell content based on value form another worksheet. Part of whether this is feasible or what you want might be are you planning on keep...

Microsoft Document Connection gives error "Only connections to SharePoint or SkyDrive servers are supported."
Trying to connect to my SharePoint server using Microsoft Document Connection with Mac Office 2011. Getting this frustrating error: "Only connections to SharePoint or SkyDrive servers are supported." I have the full version of Mac Office 2011 (not student/teacher edition) and am running MOSS 2007 Service Pack 2 (12.0.0.6421) on my server. I have no problem connecting to SharePoint using anything else. Does anyone have any ideas? I have tried the basic authentication option, too but this has has no effect. Thanks in advance! Toby On Jan 25, 8:22=A0pm, Toby <tobybore...@gmail...

Is there a way of have more than 255 characters in Excel 2000+ Footer
Hi guys I am trying to export report from my program to excel, but in my reports I have footers that have more than 255 characters. Does anybody knows how to have more than 255 characters in the footer in Excel. Thanks ! Harry ...

Outlook 2003: Cannot connect to Exchange Server 2000
We have installed Outlook 2003, and have configured it to connect to Exchange Server 2000. But each time we try to open any Exchange mailboxes in Outlook, we get the following error: "The set of folders could not be opened." The details button shows the follows: "This error usually appears if the OST or PST file you are using is: -Unavailable -Protected with file permissions -On a share on a server and the network is down -Corrupt To correct this problem, store the OST or PST file on the local computer. You may also want to run the scanost.exe and scanpst.exe tools t...

CRM access 09-27-05
Hi all, I have an interesting one. The CRM server O/S is SBS03, it is hosted in an offsite internet provider. To access the CRM, this was performed via PPTP VPN to the server and then routed via an internal RAS IP to the server's IIS, i'm not that experienced in this but it seems to be functioning ok. Each of the pc's were connected to the VPN, added to the domain, tested ok for web CRM and SFO installed. All ok untill recently. Now they are having times (blackspots) where it seems that the IIS locks and stops all CRM and Sharepoint action. This can happen at random with ...

Re: pb linking excel file (or is excel nuts?)
Hi, i present myself, poze, student in computing (final year computer studies) I've got a couple of excel file in which I pick up a certain amount of information (using =('C:\folder\[file.xls]where'!$Line$Column)) Right... That works perfectly. Some of the field are empty. So I decided to check with : WENN(ISTLEER( sorry... that's german. this is the function if(isblank()) One more time, that works. (so what's my question???) Well, the fact is that the file in which I take the information has been modified on an other computer. I'e taken back the file. If I try now t...

SBS Connectivity issues
Experiencing intermittent delays and dropped connections to Small Business Server. Config is: SBS2003 SP2 (all current patches) running on HP ML110 server; 4 PCs onsite - 3 used by onsite staff; 1 used by remote user to access server via RWW. Users run basic Office apps, Outlook, Quickbooks, internet access. Also have an Avaya VIOP phone system (4 phones) on the network. Everything has been in place for almost 4 years. Having more frequent connectivity issues. Onsite PCs show delays in Outlook connecting to Exchange ('Trying to connect ...). Remote user reports...

Why are all my Excel docs in Google Groups opening as Read Only?
It looks like the Excel docs are all Excel 97-2003 files. I'm on Vista. Is this a compatability issue? The Google Group admin for these docs says it is not an issue with the security access level. ...

ODBC Connection Not Connecting
I am using the below code to 1) automatically connect to a Microsoft ODBC for Oracle connection called "FIN_CUR" and 2) run an query on a linked table in FIN_CUR The issue i am having is that the ODBC connect part of the script appears to be working, but when it goes to try to run the query I still get the popup prompting for user name and password to make the connection to the ODBC. I have verify that the user name and password are correct, but don't know what I am missing. Here is my code Public Sub OraConnect() Dim strErr As String On Error GoTo...

Excel Formulas #3
I have a sheet that tracks time alotted for work schedule over several Months. I need to force any formulas that return a Saturday or Sunday, to Monday. With your date or formula in A1: =A1+(WEEKDAY(A1)=7)*2+(WEEKDAY(A1)=1) will change Saturday or Snday to the following Monday. -- Gary''s Student - gsnu200820 "LH4045" wrote: > I have a sheet that tracks time alotted for work schedule over several Months. > > I need to force any formulas that return a Saturday or Sunday, to Monday. Same length, but saves a multiplication operation... =A1+(WEEKDAY(A1,2)>...

FRx statements exported to one Excel workbook
Clients would like to export chained FRx reports to export to one Excel workbook for easier e-mailing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=5c667c48-735e-42b4-99...

Excel
Hello, I have a four level sort that I use quite often. It is possible to "save" my sort parameters/order so I do not have to set it up each time? All suggestions are appreciated. Use a macro to sort. You can record most of the recording with a practice run, then twig it appropriately "rekoop" <anonymous@discussions.microsoft.com> wrote in message news:105cf01c43f67$d6277cf0$a601280a@phx.gbl... > Hello, > > I have a four level sort that I use quite often. It is > possible to "save" my sort parameters/order so I do not > have to set ...

Recommendation for Good Books to Learn Access 2003 and Visual Basic for Applications
I am a experienced mainframe computer programmer but I have no experience with Access or VBA. I need to learn Access 2003 and VBA for a project that I am taking over from a contractor who was fired. I am familiar with relational database concepts and SQL, but again I have very little actual experience using them. I believe the databases involved are already set up and the data dictionary part of the application is already working. I know some java so object oriented programming, concepts, and design patterns are not new to me. I am told that the actual VBA code involves ...

Powerpoint value in Excel
Helo, I'm looking for a way to connect a text (value) from a powerpoin presentation to one excel cell. Is this possible ? Thank you, Ott -- Message posted from http://www.ExcelForum.com ...

WinXP-Internet gateway blocking internet access
Win XP Media Center Edition SP2 Comcast - ISP Linksys wireless router My friend has this problem. He cannot access the internet from the desktop PC (above). It is wire connected to the router. IPCONFIG returns proper addressing 10.10.10.101; gateway 10.10.10.1 The 2 other systems in his house are wireless to the Linksys router and work just fine. a PING to www.yahoo.com just times out. PING to 10.10.10.2 shows 4 successes. In network connections I noticed that the Internet Gateway is present. The "delete" is greyed out. Could this be causing the problem since...

Outlook Win98SE Trying to Connect to Server time out. IBM 390E & Rogers.com
I have loaded Win98SE and Office 97 on an IBM 390E laptop using IBM Etherjet 10/100 network card connected to a D- Link 714P+ 4-port wire&wireless cable router. Network file transfers work fine, IE 6 works fine. Outlook often hangs when it loads and trys to connect to the (Rogers.com) server to download email messages. I contacted Rogers tech support and all settings are correct. If I take the router out of the loop and connect directly to the cable modem - Outlooks works like a charm & fast downloading of messages. I have 3 other PCs connected on the LAN and all work fine...

Excel Unable to Open Browser #2
SFEcon is website that stages MSWord and Excel documents for download. These documents contain links back to the site allowing ou correspondents to view web pages that explain things in the documents. We recently upgraded our operation with a new P4 computer, and fin Excel behaving strangely in that clicking on a link in one of ou spreadsheets yields the following error: Microsoft Excel ´┐ŻUnable to open http://www.sfecon.com/GEP.htm. Cannot locate th internet server or proxy server.´┐Ż The problem does NOT occur with links embedded in MSWord documents. We are able to isolate the error furt...

Simultaneous socket connections. Is there a limit?
My client wants me to write an application that maintains 30 or 40 simultaneous socket connections to different hardware devices. For each device the throughput requirements are very small since only some small packets are sent occasionally at random intervals. Is there a simultaneous limit? 30 or 40 is far more then I have ever done. I don't want any surprises. Is there a limit? I assume XP Professional would handle this. Correct? Would XP Home be any different? My laptop has XP home. To certain incoming ports, its 10, although there are several cracks out their to remove this...

Automatically increment a unique number when filling in a userform in excel
Hi all, I have created a userform in Excel to enter data into a worksheet. In Column A, I have a unique number beginning at number 1 and incrementing everytime data is entered into Column B, C, etc. I would like to have a unique field in the userform that will increment the number in Column A (with no input from the user) and for the user to enter the remaining data into the userform that will fill in Columns B,C, etc for the row as the unique incrementing number. Is this possible? Thanks in advance for helping. Calculate it with iId = Cells(Rows.Count,"A").End(xlUp...

VPN can't connect to workstations
I am running a 2k3 network and I am trying to connect my laptop w/ Vista to it using VPN. I can connect to the server and use server shares with no problem. My problem is that it cannot connect to the workstations . Would appreciate any input on that. "john" <johnbahran@hotmail.com> said this in news item news:b4419f87-386b-41de-876a-e3546096ecc2@c34g2000yqn.googlegroups.com... > I am running a 2k3 network and I am trying to connect my laptop w/ > Vista to it using VPN. I can connect to the server and use server > shares with no problem. My problem is ...

server connect
When I first start up Outlook 2003, it contacts the mail server and everything is hunky-doory. However, when attempting to next access the mail account by the send/receive access or in background, it indicates that it cannot locate the server and gives me error (0x80004210A). This error number is not consistent other than for the 0x800 portion. The location does not change, so how can it find it initially, and not later on? I'm confused. What type of mail server? POP3, IMAP, Exchange, HTTP, Other...? Does the same happen when you disable your firewall and/or virusscanner (integra...

How do I remove duplicated information in 2 columns of Excel?
In Excel 2003 I have two columns, Names & Places, some names have duplicated places against them and I want to delete these rows completely to give me an index of which places I can find these names. As I have 300K names and many have the place duplicated 10 or 20 times, and some places have the names duplicated, I am eager to find an easy way remembering to keep the information in their pairs. Hi Graham, see Filter Unique Records http://www.contextures.com/xladvfilter01.html#FilterUR you might want to also look at the following , but it is a filter you want Duplicate And Un...

Remove Date Timestamp in Excel?
Hi there, I was wondering if someone can help me out. I am using a csv file and want to calculate if things were done on time or not. Example: In the A column I would have "Date Expected" In the B column I would have "Date Completed" In the C column I have an IF formula such that if B<= A then "Meets" otherwise "Not Met" But I have a problem since I get the data from a csv file. The timestamp can give the impression that something does not meet. Example Date Expected: 09/18/2006 1:00am Date Completed: 09/18/2006 2:00am As per the formula it w...