Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Saving data from MsAccess to Sql table

Posted on 2013-05-14
6
Medium Priority
?
371 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 600 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

571 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