Solved

Saving data from MsAccess to Sql table

Posted on 2013-05-14
6
310 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
  • 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:programmher
Comment Utility
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
Comment Utility
Rewrote module.  Closing ticket.
0
 

Author Closing Comment

by:programmher
Comment Utility
Rewrote module.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now