Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

We are moving from Windows 7 to Windows 10, and from Office 2010 32-bit to Office 2016 64-bit. We have recently had some issues with our testing of Access databases in the new environment. When opening a database, we are getting a 'First Things First' pop-up pop-up which gives us the option to install some updates OR proceed with caution. Now we have had a variety of results when continuing from here:
- click 'Ask Me Later', and things have worked fine
- click 'Ask Me Later', and things have imploded (forms not working initially then could not open the database)
- click 'Install Updates' and things have worked fine

On the database that imploded, as it was opening initially, the forms were coming up with an 'Error' pop-up and the only option we were given was to click Ok. Closing and then trying to re-open the database using Access 2016, we got a syntax error message. This message also came up when we tried to open the database in Access 2010.  We eventually figured out that some references were updated, was able to restore the database and get it working again in Access 2010.

When we clicked 'Install Updates', I was expecting quite a delay in opening the database, but that was not the case. It opened almost immediately, so I'm not sure what updates were installed.

So my questions are:

- What is the proper (correct) way of moving Access DBs from 2010 32-bit to 2016 64-bit? Conversion tool?
- What updates are done when you select 'Install' on the …
0
Hello, I have an Accounting access program I am upgrading to win 10. All the queries run except one that has a lot of Where parameter (5 of them).

I keep receiving the error "System resources exceeded".

I can not change the query due to the business requirements.

Any advice?
0
Hi,

I am looking for Access VBA script which can create link table to an excel spreadsheet out side the database.

If above is possible, I need an Access VBA script which can disconnect the linked table from the database.

Please let me know

Thank you
A
0
I am using a simple web based form to submit information to an Access DB. Here is a sample of the code:

<%
Dim myConnString
Dim myConnection
Dim mySQL

myConnString = Application("MyDBTable_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString
      
mySQL= "INSERT INTO Table "
mySQL= mySQL & "(status,pub_status,...
mySQL= mySQL & Request.Form("pub_status") & "','"
...

myConnection.Execute mySQL
myConnection.Close
Set myConnection = Nothing

If draft = "No" Then
...

The problem I am noticing is that every once in a while, random characters are appearing in the DB. For example the field called draft...I give the user to dropdown box to choose Yes as the box defaults to No. There's no way to specify anything besides Yes or No and as you can see from the code, I have condition statement based on which they choose. Well something happen three straight times to this users information and when I looked at the database the entry that was submitted was "Not". How did Not get into the field when the only two options were Yes or No?

I started looking around and another one of my fields which identifies which city our office was located, in this case Columbia, had entries like Columbial, ColumbiaR, Columbiae and my favorite Columbia&#10642%. This is a drop down as well. Any idea why or how these random character are getting added to the data entered.

BTW, this is a random occurrence, it does happen all the…
0
Hi

I am looking for an Access VBA script which can apply conditional formatting on Excel worksheet columns based on cell values.

Please let me know your thoughts.

Thank you
A
0
I am as clear as mud on which is the best (newest?) driver to use to connect Access to SQL Server. I've been using DRIVER=SQL Server. I recently tried DRIVER=ODBC Driver 13 for SQL Server which seems to work fine. I came across Microsoft OLE DB Driver 18 for SQL Server but I haven't been able to figure out how to use that. I get a Can't find installable ISAM error.

I am looking for advice on the best driver to use. I would like to know if I can use Microsoft OLE DB Driver 18 for SQL Server and how.

Thank you.
0
I am using Access 2013 and an email DLL library called Redemption. They work together very well and allow me to send emails from within my Access program to all my clients without any Outlook Security warning or any intervention whatsoever. It's seamless. We send all types of emails to our clients, but up until this time most of the emails have been only text.

Now, I want to up the game and send HTML emails with embedded pictures and links.  Does anyone have any experience with this using Redemption or another method? I am open to different options.

Ideally, I want to have the email generated in code and be sent to any client with the click of one button. The access software gets the client's email address from their record automatically. I don't mind having to program the email contents each time for a particular email solicitation.

If there was third party template generator I could just copy that would be great, but I haven't found one to work. For example, I am unable to recreate a mail chimp template in code.  It's not worth moving to an external email engine as I want this to be 1 or 2 client emails that are specifically generated for that client on the fly. Like, you get off the phone with a client and you just finished discussing a particular product and you want to immediately send a product spec sheet with pictures and links. The next client would have a different product, etc.

Any thoughts or idea would be greatly appreciated.
0
I am trying to develop an API call to Jet.com to retrieve my "token" so I can begin uploading products.  I have referenced the developer docs below, but I keep getting error messages. Is it necessary to really add the spaces and line feeds, or is this just for readability. Trying to get past step one. Any help would be appreciated. Thank you.

Definition
https://merchant-api.jet.com/api/token
Examples
Token Request
{
  "user": "5AF8F18CC1F635F34015EA871D3482351282CE37",
  "pass": "TRMct3xCvJX3P+SIMH4/CyJH3M9giFPtvRqRr8uNgPJz"
}

Open in new window



The following headers are required when making any call through the merchant API unless otherwise specified. The {id_token} will be returned after completing the "retrieve token" step

Header                                    Value        
Content-Type               application/json
Authorization              bearer {id_token}

---> My code

Body = "{" & """User"": " & """" & JetAPIUserKeyTest & """" & ", ""pass"": " & """" & JetSecretKeyTest & """" & " }"

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    strURL = "https://merchant-api.jet.com/api/token?" & Chr(13) & Chr(10)
    objHTTP.Open "POST", strURL, False
    objHTTP.setRequestHeader "Content-Type", "application/json"
    objHTTP.setRequestHeader "Authorization", "bearer {id_token}"
    objHTTP.send (Body)

Open in new window

0
I am trying to run some simple API calls to Jet.Com to first get my token and then publish some product I have for sale. I am using Access 2013 and I am getting close, but know I am leaving our some import information possible regarding the header or XML format. Below is my code and any help would be appreciated. Please forgive my lack of detail in the code, but with a little help I believe I can lean it. See code below:

  strURL = "https://merchant-api.jet.com/api/token?"
    strURL = strURL & "User=" & JetAPIUserKeyTest & "&pass =" & JetSecretKeyTest '& """" ''" '" '& "}"

    Dim htm As Object
    Set htm = CreateObject("htmlFile")
    With CreateObject("msxml2.xmlhttp")
       ' .Open "GET", strURL, False
        .Open "POST", strURL, False
        .send
        Response = .responseText
     End With
Thank you
0
I'm struggling with a label report in Access - A4, 2-column, 105mm x 74mm, no margins. Everything line up nicely when printed directly - when using the built-in PDF export, it's like some margins are Applied and everything is no long in its place when printed from the pdf - I've done numerous Little adjustments to try and correct it, but with no luck so far. Please advice :) It's the same result when run from another system.

DoCmd.OutputTo acOutputReport, rpt, "PDF Format (*.pdf)", strPDFTmp, False

Open in new window

0
Hi

When I try to format data set on excel sheet using below MS Access VBA script, I am getting run time error 1004, "Unable to set the HorizontalAlignment property of the Range class.

Public Sub North()
Dim ObjExcel  
   
    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\aa\bb\North.xlsx"

    Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets(2)
    ObjSheet.Activate

         ObjSheet.Range("1:1").HorizontalAlignment = xlCenter
         ObjSheet.Range("1:1").VerticalAlignment = xlCenter

Please have a look

Thank you
A
0
I am looking for advice on how to optimize my code.  It is working now, and happily generates the output shown-- but at this moment, I've only written four levels.  I need ten total, and it seems to me that I should be able to reuse the lines of code by passing them different values, depending on what part of the hierarchy has focus at any given time.

This is the code that I would want to re-use;  XX and YY would be replaced by integer values:

    'LevelXXCheck:
    If DCount("*", "HSTProductionReportTb", "ManagerID = '" & rsYY.Fields("EagentID") & "'") > 0 Then
        Set rsXX = db.OpenRecordset(strSQL & XX & " and ManagerID = '" & rsYY.Fields("EagentID") & "'")
        rsXX.MoveFirst
        Do While rsXX.EOF = False
        strInsertSQL = "Insert into HSTProductionHierarchyTb (AgentNa, EAgentID, NAPXX, TeamNAPXX) select '" & rsXX.Fields("AgentNa") & "', '" & rsXX.Fields("EagentID") & "', '" & rsXX.Fields("NAPSum") & "', '" & rsXX.Fields("TeamNAPSum") & "'"
        DoCmd.RunSQL strInsertSQL
    'Insert Next-Level SubordinateCheck here
'end of LevelXXCheck:
        rsXX.MoveNext
        Loop
        rsXX.Close
        Set rsXX = Nothing
    End If

I'll be grateful for any advice or recommendations.  :)

Paul


Example of formatted output

Private Sub btn_Hierarchy_Click()
'loops through the Production table, selecting the top-level manager, that manager's subordinates, and so on down the chain
'names, IDs, NAP, and TeamNap are inserted into a 

Open in new window

0
Is it possible to grow a text field on a form?  I know you can on a report but I don't seem to be able to make it happen on a form.
0
I have a query that lists last names of people.  But I need to show them on a form with the names followed by a comma and a space.  So for example... Steve, Bill, David, George, Susan, Martha, etc.  How can I do this?
0
In open office how to insert one shape inside other

as attached i have big rectangle
I like to insert small circle into that rectangle
how can i achieve it

please advise
OpenOfficeShapes.png
0
visio 2013 standard i opened blank diagram,

I do not see any shapes like circles, rectangles, person, arrows etc on the left hands side

how to enable them and keep them there all the time so that whenever i open visio i can just drag and drop and make the diagram
please advise
0
I am having an issue when exporting Access Reports to PDF.  I am using the 'DoCmd.OutputTo acOutputReport, stdocnameTrend, acFormatPDF, FileNamePDFDets, , , , acExportQualityPrint function.  

The function is working however when I open the PDFs not all of the images are showing, I have looked at the "Display When" and they are all set to always.  When I print to pdf it works, but I can not find the code to automate printing to PDF...

Anyone have any success with this?
0
i see some templates here.
i downloaded zip and unzipped and copied those contents and placed at
C:\Users\xyz\Documents\My Shapes

http://softwarestencils.com/uml/index.html

any other visio similar templates to reuse existing shapes and sequence diagrams etc

please advise
0
I am trying to list files from folder\subfolder from this sharepoint folder.
SharepointAddress = "https://centralhub.XXXXX.com/team/CHS/SRS/Account Set Up Templates/"
I would like to do this from a button on a form and put the information in a table for exporting.
I have never done anything like this before thru a network except normal dir stuff.
Does anyone have any code that can help me do this. I would greatly appreciate it.
0
Hi,

I am trying to automate report (excel spreadsheet) production using access vba script and windows task scheduler.
I am getting error message at beginning of the scripts itself(given below).

SetAttr "Z:\Report_New.xlsx", vbNormal

(because I apply SetAttr "Z:\Report_New.xlsx", vbReadOnly at the end of all scripts.)


"Z" is a mapped shrepoint drive. The run time error 75 tells Path/File access error. But when I run the script manually there is no issue. When I left it to run over night, vba editor throws this error message.

Please let me know  if you have any suggestions
Thank you
A
0
Can't load any active x objects
screen-shot.png
0
Hi Experts,

I have a SQL view that is being linked to my Access app.
That view has a column named SNVNum, defined as follows
CAST(REPLACE(dbo.Skilled_Nursing_Visit_Note.SNV_ID, 'snv', '') AS int)

Open in new window

Now when I open the view and sort by that column it works perfectly fine in db container.
it also works fine when running the following in SSMS.
Select * from vw_SkilledVisitNotesFilterFrm Order by SNVNum

Open in new window

However when I assign the form the following SQL as record source.
        Me.RecordSource = "Select * from vw_SkilledVisitNotesFilterFrm  Order by SNVNum"

Open in new window

it gives me the error attached.
Untitled.png
0
Hi,
The screen is Shrinking when run my app on laptop always. So it won't show the bottom part of application on the laptop screen. Look my images may it will clarify my problem.
please help me.
screen One, is what should I see.
screen One what should I seeScreen Two, what happen when I run my application on a laptop, where you can see the buttons down in the form is not shown
screen Two
1
I want to write a vba macro that does the following

link a table from sql server to access database, embed/program and execute access query in vba and remove the linked table

My code so far is this.


Sub Update()
Dim Con       As ADODB.Connection
Dim RS        As ADODB.Recordset
Dim myCommand As ADODB.Command



Dim AccessFile As String


Set Con = CreateObject("ADODB.Connection")

Sheets(1).Select
ActiveSheet.Select

'AccessFile = Range("b3").Text & "\" & Range("b4").Text & ".accdb"
AccessFile = Range("b3").Text & "\" & Range("b4").Text & ".mdb"

With Con
  .Open "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" & AccessFile
End With

'cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & Conn

Set myCommand = CreateObject("adodb.command")

''************ link table dbo_production_values from a sql database somewhere on the network                 <-----------------------

With myCommand
    .ActiveConnection = Con
    .CommandType = adCmdStoredProc
    '.CommandText = "qry_Daily_Production"
    .CommandText = "SELECT AC_PROPERTY.propnum, Prod.client_day, Prod.allocated_oil_production, Prod.allocated_water_production, Prod.allocated_gas_production, _
    Prod.api FROM AC_property INNER JOIN dbo_production_values AS Prod ON AC_property.api=prod.api WHERE prod.client_day >= DateAdd ("d", -300, Date());""
   '.Parameters.Append .CreateParameter("paramDateInput", adDate, adParamInput, , Sheets("DataEntry").Range("D5").Value)
    Set RS = .Execute()
End With

Open in new window


''************ deink table dbo_production_values from a sql database somewhere on the network                 <-----------------------



' continue with your checks if rs is empty etc.
End Sub



Please help me with the syntax..

Thank you.
0
What are the known problems when upgrading an old ms access database to ms access 2016?
0

Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.