Solved

Saving data from MsAccess to Sql table

Posted on 2013-05-14
6
336 Views
Last Modified: 2013-05-28
My client has an access app that generates and sends emails and then saves the emails to a folder on their network.  Now I need to include logic to save these locations to a SQL table.  I have done the below but nothing inserts into my SQL table and there aren't any errors:

dim db as database
dim strconnect as string
dim strsql as string

strconnect="odbc;driver={sqlserver};server=myserver;database=my database;trusted_connection=yes"
strsql="insert into [myserver].[tablename] (column1,column2) values (value1, value2);"
db.execute strsql, dbfailonerror

Why won't my record insert?
0
Comment
Question by:programmher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 150 total points
ID: 39166116
Is this the actual VBA code you are using or just pseudo code?

Your SQL Statement doe not look correct.

What are the data types for the two columns?

If the columns are both text then you will need to use this format:

strsql="insert into [tablename] (column1,column2) values ("value1", "value2");"

Open in new window


Assuming you are substituting column1 and column2 for the actual column names, the cvaules will need to be inserted from somewhere.

Example:

dim strValue1 as string
dim strValue2 as string

strValue1 = "Data 1"
strValue2 = "Extra Data 2"

strsql="insert into [tablename] (column1,column2) values (" & Chr(34) & strValue1 & Chr(34) & ", " & Chr(34) & strValue2 & Chr(34) &  ");"

Open in new window

0
 

Author Comment

by:programmher
ID: 39175899
Hitechcoach,

The VB code is actual code.  The SQL statement is pseudo.  My SQL table has many columns.  Author_name, ISBN, publisher, published_date, etc.  
So, if I understand your comment, I need to declare all values associated with each column and use the formatting you included in your example code and that will result in my record successfully inserting into my SQL table?
0
 
LVL 21
ID: 39176251
That sounds correct. Without seeing your actual work it is hard to say if you have figured it all out.

TIP: To get the best and quickest help possible post your actual code. Replace confidential data with something that will help us understand your project.

Depending on what you are doing and how many fields you have, it may work better with a recordset.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:programmher
ID: 39189395
TheHiTechCoach,

I still can't get  my record to insert into my SQL table.  I do not get any errors.  Below is the code.    I would be happy to get the record to at least insert with null values and I can tweak the formatting.  Can you identify my error?

 Dim db As Database                    
  Dim strConnect As String
  Dim strSQL As String
  Dim strValue1 As String
  Dim strValue2 As String
  Dim strValue3 As String
  Dim strValue4 As String
  Dim strValue5 As String
  Dim strValue6 As String
  Dim strValue7 As String
  Dim strValue8 As String
  Dim strValue9 As String
  Dim strValue10 As String
  Dim strValue11 As String
  Dim strValue12 As String
 

strConnect = "ODBC;DRIVER={SQLServer};SERVER=GEMINI;DATABASE=MED_PSY1;Trusted_Connection=YES"
       strSQL = "INSERT INTO [MED_PSY1].[sysdba].[tbl_main_info] (TECHID, TECHDATE, TECH2ID, CONTACTID,SED_ID,DESCRIPTION,DescTYPE,SIZE,NAME,DATA,pharmaID,DE_pharmaID) VALUES ((" & Chr(34) & strValue1 & Chr(34) & "," & Chr(30) & StrTodayDate & Chr(30) & " ," & Chr(30) & strValue2 & Chr(30) & "," & Chr(30) & strValue3 & Chr(30) & "," & Chr(30) & strValue4 & Chr(30) & "," & Chr(30) & strValue5 & Chr(30) & "," & Chr(30) & strValue6 & Chr(30) & "," & Chr(30) & strValue7 & Chr(30) & "," & Chr(30) & strValue8 & Chr(30) & "," & Chr(30) & strValue9 & Chr(30) & "," & Chr(30) & strValue10 & Chr(30) & "," & Chr(30) & strValue11 & Chr(30) & "," & Chr(30) & strValue12 & Chr(30) & ");"
0
 

Accepted Solution

by:
programmher earned 0 total points
ID: 39192951
Rewrote module.  Closing ticket.
0
 

Author Closing Comment

by:programmher
ID: 39200680
Rewrote module.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question